FINANCIAL FUNCTIONS PART II

[Brent Starks]

First a collective thanks to Ron Rosenfeld, Harlan Grove,
and Fred Smith for collaborating on my original problem
posted 9/3/04. It was interesting and informative seeing
how you each brought a different perspective to the
problem. I settled on Ron's UDF as the simplest and most
accurate solution for what I needed.

The original parameters:
Tax rate=27%
ROR=10%
Payment=$100/mo
Compounding periods/yr=12

The problem: How to show the net future value on taxable
investment accounts?

Ron correctly assumed that I computed taxes annually, not
quarterly, the payment is made at the beginning of each
period and that whole years are used for the term of
investment. That works fine for my purpose--regardless
of how much taxable income is shown. The results of the
UDF exactly match my figures for 10 years (as far as I
ran the numbers on my calculator). Here are the net
future values I got for each year:

Year 1: $1248.93 Year 6: $9080.20
Year 2: $2593.33 Year 7: $11023.27
Year 3: $4040.49 Year 8: $13114.83
Year 4: $5598.28 Year 9: $15366.26
Year 5: $7275.15 Year10: $17789.80

I am pretty comfortable working with excel functions, but
a real neophyte when it comes to writing code for VBA.
And that leads to my next problem--besides the monthly
contributions, I need to add the PV argument to the
calculation to allow for a one-time, lump sum payment. I
have included Ron's UDF here for reference.

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

I discovered, after several attempts to modify Ron's
code, that this is harder than it would seem. If I
understand the UDF code correctly, it determines the FV
of a series of payments, computes the growth (net of
payments) on that amount, figures annual taxes on the
growth, subtracts taxes from the FV just computed, and
then uses the resulting difference as the PV for the next
year's growth calculation. This is repeated for however
many years contributions are made.

What I am stumped on is how to add a one-time payment in
the first year only, use that value in all FV
calculations and not have it show as a new payment in
each susequent year for the term of the investment.

Any ideas?

I greatly appreciate any help you provide.

Brent Starks

 

[mangesh_yadav]

Function FVafterTax(Pmt, Rate, TaxRate, NumYrs, newPV) 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
If i = 1 Then
Principal = FVafterTax - Pmt * 12 - newPV
Else
Principal = FVafterTax - Pmt * 12
End If
FVafterTax = FV(Rate / 12, 12, Pmt, -FVafterTax, 1)
Gain = FVafterTax - Principal
Tax = Gain * TaxRate
FVafterTax = FVafterTax - Tax
Next i

End Function

This should do the trick. Also I am not sure about the sign of th
newPV in the code part
Principal = FVafterTax - Pmt * 12 - newPV
If the pmt and the newPV are both incoming (or outgoing) then th
formula is correct, if they are opposite, please put a + sign fo
newPV

- Manges

 

[Ron Rosenfeld]

What I am stumped on is how to add a one-time payment in
the first year only, use that value in all FV
calculations and not have it show as a new payment in
each susequent year for the term of the investment.

I've not checked this but it seems that in the UDF, you merely have to add that
first payment to the starting FVaftertax variable. So:

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

FVafterTax = -FirstPmt

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
========================

This assumes that you also make the $100 Payment during the first month. If
that is not the case, than just subtract your regular payment from your first
payment (i.e. in your example, make it a $900 Payment).

As is the Excel convention, FirstPmt should be entered as a negative number.

I note that this UDF gives somewhat different answers in years 7-10 than what
you posted (pennies). I presume this is due to some kind of rounding
difference between your calculator and Excel. Is this an issue that needs to
be looked into?


--ron

 

[Dana DeLouis]

Hello. I don't have an answer, but I was just messing around with the
equation. This factors out two constants from the loop.

Function FV_v2(p, r, tx, yr) As Double
Dim j, k1, k2

r = 1 + r / 12
tx = 1 - tx 'What you keep

k1 = p*(12+((r*(r^12-1))/(r-1)-12)*tx)
k2 = 1+(r^12-1)*tx

For j = 1 To yr
FV_v2 = k1 + k2 * FV_v2
Next j
End Function

Sub TestIt()
Dim yr As Double
For yr = 1 To 10
Debug.Print _
yr; FormatNumber(FV_v2(100, 0.1, 0.27, yr), 2)
Next yr
End Sub

Returned:

1 1,248.93
2 2,593.33
3 4,040.50
etc...
9 15,366.21
10 17,789.74

HTH
Dana DeLouis

 

[Dana DeLouis]

Oops. Here's an attempt to eliminate the loop:

Function FV_After_Tax(p, r, tx, yr) As Double
Dim j, k1, k2

r = 1 + r / 12
tx = 1 - tx

k1 = p*(12+((r*(r^12-1))/(r-1)-12)*tx)
k2 = 1+(r^12-1)*tx

FV_After_Tax = (k1*(k2^yr-1))/(k2-1)
End Function


? FV_After_Tax(100, 0.1, 0.27, 10)

17789.74

HTH
Dana DeLouis

 

[Ron Rosenfeld]

I've not checked this but it seems that in the UDF, you merely have to add that
first payment to the starting FVaftertax variable. So:

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

FVafterTax = -FirstPmt

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
========================

This assumes that you also make the $100 Payment during the first month. If
that is not the case, than just subtract your regular payment from your first
payment (i.e. in your example, make it a $900 Payment).

As is the Excel convention, FirstPmt should be entered as a negative number.

I note that this UDF gives somewhat different answers in years 7-10 than what
you posted (pennies). I presume this is due to some kind of rounding
difference between your calculator and Excel. Is this an issue that needs to
be looked into?


--ron

Or you could use a modification of Harlan's worksheet formula:

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

==============

Be aware that Harlan's formula and my UDF give the same absolute result, but
the signs are opposite.


--ron