FINANCIAL FUNCTIONS

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?

Hi Brent,

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

If this is not the case and you need to find the future value as of
today for each year, then this can be done in one operation by giving
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 of
1248.93

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

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

- Mangesh


---
Message posted from http://www.ExcelForum.com/

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

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


"Brent" wrote in message
...
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?

On Sat, 4 Sep 2004 20:24:43 -0600, "Fred Smith" wrote:

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

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" wrote in message
...
On Sat, 4 Sep 2004 20:24:43 -0600, "Fred Smith"
wrote:

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

On Sun, 5 Sep 2004 10:08:14 -0600, "Fred Smith" wrote:

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

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)

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


"Ron Rosenfeld" wrote in message
...
On Sun, 5 Sep 2004 10:08:14 -0600, "Fred Smith"
wrote:

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

On Mon, 6 Sep 2004 08:20:02 -0600, "Fred Smith" wrote:

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

On Fri, 3 Sep 2004 20:34:24 -0700, "Brent"
wrote:

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, altF11 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

Ron Rosenfeld
"Brent" wrote:
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.
...

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.

For example, assuming all payments at the beginning of the month and
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. The
ideal would be using the after-tax future value at the end of each
quarter of payments within each quarter, then taking the future value
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 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)


---
Message posted from http://www.ExcelForum.com/