Annuity function

[Jake]

Hi,
I'm creating a chart for a client and want to make sure I'm using the
correct function. I would like to calculate the monthly amount that can be
withdrawn from an investment over time. For example if an investor has
$950,000 now and wishes to have $50,000 at the end of 10 years what amount
can he withdraw every month at an interest rate of 5%?
If I use the PMT function I get an amount of $6,648.22, is this accurate for
the variables and an annuity?
Thanks,
Jake

 

[Ron Rosenfeld]

Hi,
I'm creating a chart for a client and want to make sure I'm using the
correct function. I would like to calculate the monthly amount that can be
withdrawn from an investment over time. For example if an investor has
$950,000 now and wishes to have $50,000 at the end of 10 years what amount
can he withdraw every month at an interest rate of 5%?
If I use the PMT function I get an amount of $6,648.22, is this accurate for
the variables and an annuity?
Thanks,
Jake

You should post the formula you used. It doesn't make sense because, if I
understand the problem, the $950,000 is earning 5% interest on the balance
after each withdrawal.

$6648.22 * 120 should equal more than $950,000 and it does not.

So either my interpretation of your problem is incorrect, or there's something
I'm not understanding.


--ron

 

[Myrna Larson]

Using the PMT function, I get $9,754.23 per month. The formula I used is

=PMT(5%/12,120,-950000,50000)

The interest rate is converted to a monthly rate, and the number of periods is
in months.

One way to check your result is to note that if the principal were earning NO
interest, withdrawing 900,000 over 10 years = 90,000 per year = 7,500 per
month. With interest, the payment will obviously be greater than that.

What is your formula?

 

[Jake]

I'm sorry, I posted the incorrect result, as you have noted. Using the
function: PMT(.004166667,120,950000,50000), I get (10,398.22). My concern
is am I using the correect formula to calculate the value?
Thanks,
Jake

 

[Ron Rosenfeld]

I'm sorry, I posted the incorrect result, as you have noted. Using the
function: PMT(.004166667,120,950000,50000), I get (10,398.22). My concern
is am I using the correect formula to calculate the value?

The PV and FV need to have opposite signs.

By convention, cash you pay out, which would be the investment or PV in this
instance, is given a negative sign. Cash you receive, which would be the
monthly payments and the final balance, or FV is given a positive sign.


--ron

 

[Myrna Larson]

The convention for signs is that money you pay out is negative, money you
receive is positive. If the future value is non zero, then ordinarily the
present value and future value have opposite signs.

In your case, the $950,000 should be negative and the $50,000 positive. i.e.
in the beginning you deposit $950,000 with the bank or annuity company (a
pay-out, negative), they make the calculated payments to you (the payment
result is positive), at the end they give you back the remaining $50,000 from
your original $950,000 deposit. The formula is:

=PMT(5%/12,10*12,-950000,50000)

The result I get with the above is +$9,754.23. You get -$10,398.22 when PV and
FV are both positive.

Here's a way to verify that the correct answer if 9754.23:

Assume you put the month in a savings acount. Set up a "checkbook" for this
account.

Use these column headers in A11: Month, Interest, Payment, Balance.
Leave A2 and B2 blank. In C2, put a trial value for the payment, say $7500. In
D2 put the initial balance of $950,000.

In A3, put the number 1 and Edit/Fill/Series to get the numbers 1-120 in
A3:A122 (this is only to verify that you are doing 120 payments).

In B3, the formula for interest earned this month: =D2*5%/12
In C3 the formula for the withdrawal/payment: =C2
In D3 the formula for the new balance: =D2+B3-C3

Copy these formulas down through row 122.

Then use Goal Seek (on the Tools menu) to find the correct payment: you want
to set cell D122 to the value 50000 by changing cell C2. The result is that C2
changes to $9,254.229705. If you take out 9454.23, you'll be 5 cents short at
the end of 10 years.

 

[Myrna Larson]

Another typo or two there: correct answer IS ... (not if); put the MONEY in a
savings account (not put the MONTH...)

 

[keepITcool]

Myrna..

We're talking money here so you need to be brutally precise !

Be aware that when the ANNUAL interest rate = 5%
the MONTHLY rate is NOT simply 5%/12 (.41667%)
but (1+5%)^(1/12)-1 = .4074%


This results in a monthly difference of $50!!!


Assuming the interest is accrued on a monthly basis.. and that
withdrawals are made at the end of the month..

the monthly amount = 9700.82
=PMT((1,05)^(1/12)-1;120;-950000;50000;0)

note the 120th payment=50000+9700,82

rounding differences < .01



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >