Finding After Tax interest rate on an investment

[veklius]

Hi, (first time posting), I could not find any related post to this problem.

Basically, I am looking for something like the "Rate" function but considering that a fraction of each period's return would be taken out and not reinvested for the the next period (also known as Tax). The only unknown here is the interest rate,

I know the present value, future value, number of period, tax rate, and payments per period.

At this point I know how to do it, but it is very inefficient as I have to guess the interest rate until I get the correct future value.

I have a feeling VBA will be needed to complete this task. Any help will be greatly appreciated!!!

 

[veklius]

Bump!

 

[shg]

If the interest rate is 7% and the tax rate is 10%, couldn't you just change the interest rate to 7%*(1-10%)?

 

[veklius]

Yes, however, the interest rate is the unkown. I am clueless as to integrate tax. I briefly looked at the other financial functions, and it doesn't seem like any of them would do this.

 

[shg]

Post an example of your data.

 

[veklius]

here is an exemple,

Tax rate is 20% of interest each year, which is not reinvested

Present Value = 0
Future Value = $300,000
Number of years = 15
Annual investment = $5,000

What is the interest rate


Like I said, I got a feeling it would be a long formula in VBA nut im not very good at coding atm.

 

[brettdj]

I'm not clear on your tax assumption piece, but using the rate function you get
=RATE(15,5000,0,-300000)
= 17.81%

which when double-checked with a FV calc
=FV(17.81%,15,5000,0)
=300000

 

[veklius]

If excel had a pre-set formula for it, it could look like the following;

=[RATE*(1-0.2)](15,5000,0,-300000)

The interest rate would have to be higher then 17.81% because it would need to compensate for the 20% tax on the gains leaving on every period, and at the same time give the same $300000 future value.

I hope this clears things up about the taxation part

 

[shg]

So the effective interest rate is 17.81%/(1-20%) ~ 22.16%

 

[veklius]

The reason that wouldn't work is because you allow the investment to grow tax free for 15 years, and then you take out the tax. the 20% should be taken out on each period. I will try and chart out an example later on so we can clarify this.