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