FINANCIAL FUNCTIONS

[Brent]

I have created a worksheet to compute future value for
various investments using different rates of return and
then showing the net return after taxes in today's
dollars. The purpose is to compare the performance of
investments over time. While the FV function works fine
for tax-deferred investments like annuities, it doesn't
work for taxable investments like mutual funds because
the growth on these funds are taxed each year so the
whole dollar doesn't compound.

My problem then is how to show growth on taxable
accounts. I have looked through the financial functions
to no avail.

The following example illustrates the problem that must
be solved. Assuming a rate of return of 10%, payments of
$100 each month, 12 compounding periods per year, and a
tax rate of 27% what would be the net value after one
year? Two years? Twenty years? The FV after one year
is $1267.03. Total growth is $67.03. Taxes on $67.03 is
$18.10 yielding a net value of $1248.93 after the 1st
year. Using $1248.93 as the new present value, and
repeating the process produces a net future value for
year 2 of $2593.33. That process must be repeated
another 18 times (or however long contributions are made
and the money stays invested).

Any ideas on how to do this? Can the FV function be
modified (tricked) into providing the desired outcome?
Or must I resort to Visual Basic?

 

[mangesh_yadav]

Hi Brent,

If I have understood your problem correctly, what you are trying to d
is find the net future value for each year and not as of today or yea
0. Atleast that is what I get from your calculations. If that is th
case, it is difficult to get the value 2593.33 in one operation. Yo
will have to do calculations for each year seperately.

If this is not the case and you need to find the future value as o
today for each year, then this can be done in one operation by givin
the formula

=FV($B$7/12,$B$6,$B$5,,1)-(FV($B$7/12,$B$6,$B$5,,1)-(-B5*B6))*B8

B5 = -100
B6 = 12 (compunding)
B7 = 10% (rate)
B8 = 27% (tax rate)

Note that for 1 year the above formula gives you the correct result o
1248.93

for 2 years you would get 2594.71 against the method you use and ge
2593.33

Note that the difference between the 2 values is that: the one yo
calculated gives the FV as of beginning of each year, and the direc
method I use give the value for FV at each year as of today's date. Fo
your method, I feel, it would be quite difficult to get the valu
directly. you might have to go for VBA.

- Manges

 

[Fred Smith]

Simply use the net (after-tax) interest rate. In your example, it's 7.3%
(10% less 27%).

 

[Ron Rosenfeld]

Simply use the net (after-tax) interest rate. In your example, it's 7.3%
(10% less 27%).

That will give an approximation. It won't be exact since taxes are only paid
once a year or perhaps quarterly, but the interest is compounding monthly.


--ron

 

[Fred Smith]

You can still calculate the effective rate properly. Taxes will likely be
paid quarterly, so convert the interest rate to an effective quarterly rate,
then subtract the tax rate. Now you have an effective after-tax rate of
return. Now, convert this to an effective rate for your payment period.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

 

[Ron Rosenfeld]

You can still calculate the effective rate properly. Taxes will likely be
paid quarterly, so convert the interest rate to an effective quarterly rate,
then subtract the tax rate. Now you have an effective after-tax rate of
return. Now, convert this to an effective rate for your payment period.

I tried it for an annual tax payment and could not get it to come out the same.

Could you show the formula for your recommendation?


--ron

 

[Fred Smith]

My formula would calculate (using the op's numbers) the rate which turns
$100/month into $1248.93. In one formula, it would be:

=RATE(nper*12,-pmt,0,(FV(rate/12,nper*12,-pmt,0,1)-(pmt*12))*(1-taxrate)+(pmt*12),1)

 

[Ron Rosenfeld]

My formula would calculate (using the op's numbers) the rate which turns
$100/month into $1248.93. In one formula, it would be:

=RATE(nper*12,-pmt,0,(FV(rate/12,nper*12,-pmt,0,1)-(pmt*12))*(1-taxrate)+(pmt*12),1)

That may be the case but that is not what the OP was requesting. He wanted a
single formula with which to compute FV at various times.

And also that computed rate does not work for two years. It gives a result of
$2592.94 vs the OP's value of $2593.33.






--ron

 

[Ron Rosenfeld]

The following example illustrates the problem that must
be solved. Assuming a rate of return of 10%, payments of
$100 each month, 12 compounding periods per year, and a
tax rate of 27% what would be the net value after one
year? Two years? Twenty years? The FV after one year
is $1267.03. Total growth is $67.03. Taxes on $67.03 is
$18.10 yielding a net value of $1248.93 after the 1st
year. Using $1248.93 as the new present value, and
repeating the process produces a net future value for
year 2 of $2593.33. That process must be repeated
another 18 times (or however long contributions are made
and the money stays invested).

Any ideas on how to do this? Can the FV function be
modified (tricked) into providing the desired outcome?
Or must I resort to Visual Basic?

I think the problem is that although taxes are deducted from the account
yearly, the moneys compound monthly, so changing the tax rate doesn't seem to
work over other than the defined period of time.

However, the problem can be solved using a User Defined Function written in
VBA.

The following makes the assumption that only whole years will be used for the
Term of the investment (i.e. that nper = NumYrs).

To enter this, <alt><F11> opens the Visual Basic Editor. Ensure your project
is highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use the UDF, enter =FVafterTax(Pmt, Rate, TaxRate, NumYrs) in some cell.

Let me know if this does what you want.

===============================
Function FVafterTax(Pmt, Rate, TaxRate, NumYrs) As Double
Dim i As Integer, j As Integer
Dim Principal As Double, Gain As Double, Tax As Double, PV As Double

For i = 1 To NumYrs
Principal = FVafterTax - Pmt * 12
FVafterTax = FV(Rate / 12, 12, Pmt, -FVafterTax, 1)
Gain = FVafterTax - Principal
Tax = Gain * TaxRate
FVafterTax = FVafterTax - Tax
Next i

End Function
===============================

--ron

 

[hgrove]

Ron Rosenfeld

I think the problem is that although taxes are deducted from the
account yearly, the moneys compound monthly, so changing the
tax rate doesn't seem to work over other than the defined
period of time.

However, the problem can be solved using a User Defined
Function written in VBA.

...

Unnecessary. If monthly payments are uniform, then Fred Smith i
correct that this could be done using the after-tax interest rate
However, this is only so because of the uniformity of payments.

For example, assuming all payments at the beginning of the month an
tax payments at the end of each quarter,

Month 1: 0 + 100 * (1 + 10%/12) = 100.83
Month 2: (100.83 + 100) * (1 + 10%/12) = 202.51
Month 3: (202.50 + 100) * (1 + 10%/12) = 305.03
_______- (305.02 - 300.00) * 27% = -1.36 => 303.67
Month 4: (303.66 + 100) * (1 + 10%/12) = 407.02
Month 5: (407.02 + 100) * (1 + 10%/12) = 511.26
Month 6: (511.25 + 100) * (1 + 10%/12) = 616.35
_______- (616.34 - 603.66) * 27% = -3.42 => 612.93

The monthly effective after-tax interest rate is

(1 + ((1 + 10%/12)^3 - 1) * (1 - 27%))^(1/3) - 1 = 0.006096921

And FV(0.006096921,6,-100,0,1) returns 612.93.

If the monthly payments werem't uniform, it gets more complicated. Th
ideal would be using the after-tax future value at the end of eac
quarter of payments within each quarter, then taking the future valu
of these quarterly figures using the after-tax quarterly interest rate
Something like the following array formula.

=NPV(((1+10%/12)^3-1)*(1-27%),MMULT(TRANSPOSE(Pmnts),
(1+10%/12)^(3-MOD((ROW(Pmnts)-1),3))
*(INT((ROW(Pmnts)+2)/3)=TRANSPOSE(ROW(INDIRECT("1:"
&INT(ROWS(Pmnts)/3))))))*(1-27%)
+MMULT(TRANSPOSE(Pmnts),
--(INT((ROW(Pmnts)+2)/3)=TRANSPOSE(ROW(INDIRECT("1:"
&INT(ROWS(Pmnts)/3))))))*27%)
*(1+((1+10%/12)^3-1)*(1-27%))^INT(ROWS(Pmnts)/3)

For annual tax payments on monthly payments, use the following arra
formula.

=NPV(10%*(1-27%),MMULT(TRANSPOSE(Pmnts),
(1+10%/12)^(12-MOD((ROW(Pmnts)-1),12))
*(INT((ROW(Pmnts)+11)/12)=TRANSPOSE(ROW(INDIRECT("1:"
&INT(ROWS(Pmnts)/12))))))*(1-27%)
+MMULT(TRANSPOSE(Pmnts),
--(INT((ROW(Pmnts)+11)/12)=TRANSPOSE(ROW(INDIRECT("1:"
&INT(ROWS(Pmnts)/12))))))*27%)
*(1+10%*(1-27%))^INT(ROWS(Pmnts)/12

 

[Ron Rosenfeld]

..

Unnecessary. If monthly payments are uniform, then Fred Smith is
correct that this could be done using the after-tax interest rate.
However, this is only so because of the uniformity of payments.

I thought that it should. But I was not able to come up with a simple
variation on the financial functions that would give the result requested by
the OP.

So given inputs of Pmt, Rate, TaxRate, and Term; with equal payments being made
monthly, interest compounding monthly, and the taxes being deducted annually,
what is the formula?

Although simplicity is in the eye of the beholder, I believe my VBA routine is
simpler than the formulas you have posted.

For annual tax payments on monthly payments, use the following array
formula.

=NPV(10%*(1-27%),MMULT(TRANSPOSE(Pmnts),
(1+10%/12)^(12-MOD((ROW(Pmnts)-1),12))
*(INT((ROW(Pmnts)+11)/12)=TRANSPOSE(ROW(INDIRECT("1:"
&INT(ROWS(Pmnts)/12))))))*(1-27%)
+MMULT(TRANSPOSE(Pmnts),
--(INT((ROW(Pmnts)+11)/12)=TRANSPOSE(ROW(INDIRECT("1:"
&INT(ROWS(Pmnts)/12))))))*27%)
*(1+10%*(1-27%))^INT(ROWS(Pmnts)/12)

Using this formula, with 24 monthly payments of $100 (Pmnts = A2:A25; and there
is a 100 in each cell), I get a result of 2473.53 versus the OP's result (and
my UDF result) of 2593.33


--ron

 

[Harlan Grove]

....
So given inputs of Pmt, Rate, TaxRate, and Term; with equal payments
being made monthly, interest compounding monthly, and the taxes being
deducted annually, what is the formula?
=FV(((1+Rate/12)^12-1)*(1-TaxRate),Years,
-FV(Rate/12,12,-Pmt,0,1)*(1-TaxRate)-12*Pmt*TaxRate,
0,0)

Although simplicity is in the eye of the beholder, I believe my VBA
routine is simpler than the formulas you have posted.

Your udf is clearer . . . for those who know VBA. It's irrelevant for those
who may not use VBA (there are some companies that prohibit use of VBA by
mere end-users). And even though my formula is very redundant, it may
possibly recalc faster than your udf due to the sluggishness of the
Excel/VBA interface.

....

Using this formula, with 24 monthly payments of $100 (Pmnts = A2:A25; and
there is a 100 in each cell), I get a result of 2473.53 versus the OP's
result (and my UDF result) of 2593.33

Some major screw-ups involving nominal rather than effective annual interest
rates, plus my Pmnts range had started in row 1, which allowed for some
simplifications. Here's the fixed formula.

=NPV(
((1+10%/12)^12-1)*(1-27%),
MMULT(TRANSPOSE(Pmnts),
(1+10%/12)^(12-MOD((ROW(Pmnts)-CELL("Row",Pmnts)),12))
*(INT((ROW(Pmnts)-CELL("Row",Pmnts))/12+1)
=TRANSPOSE(ROW(INDIRECT("1:"&INT(ROWS(Pmnts)/12))))))*(1-27%)
+MMULT(TRANSPOSE(Pmnts),
--(INT((ROW(Pmnts)-CELL("Row",Pmnts))/12+1)
=TRANSPOSE(ROW(INDIRECT("1:"&INT(ROWS(Pmnts)/12))))))*27%)
*(1+((1+10%/12)^12-1)*(1-27%))^INT(ROWS(Pmnts)/12)

 

[Ron Rosenfeld]

=FV(((1+Rate/12)^12-1)*(1-TaxRate),Years,
-FV(Rate/12,12,-Pmt,0,1)*(1-TaxRate)-12*Pmt*TaxRate,
0,0)

Thank you for that! I felt that there had to be a relatively simple answer
like this to the OP's original question. I just could not figure it out.

Best,

--ron

 

[Brent]

Hi Mangesh,

Thanks for your help. This looks like exactly what I
need. Haven't had a chance to try it out yet, but soon
will. As for running the calculation period from the
current date vice the beginning of the year, that is
fine. Thanks again.

Brent