How do I calculate a payment?

[aidesifuentes]

I have a starting investment and I have the future value that I want to
attain over a specified period. Which financial function will calculate the
payments I need to make over the specified period to reach my future value
goal?

 

[porter444]

Check out the PMT function to see if that does it for you.

 

[aidesifuentes]

Actually I've looked at the various payment functions and they seem to be
calculating unrealistic payments. Using the PMT function and the variables
below, I got a monthly payment of $35k over five years to reach $1.8million
but I started with $500k so a monthly payment of $35k seems much too high
when 60 payments of $35k will accumulate over $2million alone without the
starting $500k. What am I doing wrong?

Payment ($35,137.74)

Rate 0.83%
Periods 60
PV $500,000.00
FV $1,898,309.51
Type 0

60 monthly payments at $35k/month = $2,108,264.61

 

[Michael]

Use the PPMT formula,
Where your rate = 9.96%/12 = 0.83%
Per =1
Nper=60
PV =0
FV=1800000
Type=1
This will give you a payment of $23,077.72 which equals $1384663 plus the
500000 you have equals $1800000
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[Michael]

Actually you have to calculate the return on the $500K investment:
=FV(0.096/12,60,0,500000,0)*-1
which equals:$821,024.35
Now you only need payments to achieve (1800000-821024.35) which equals to
$978,975.65; so this is the basis for your monthly payments in the formula:
=PPMT(0.0096/12,1,60,0,978975.65,1)
Which returns a more reasonable monthly deposit or payment of $12,551.40
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

 

[aidesifuentes]

So the key is to use two formulas rather than one which is what I was trying
to do.
When using these payment functions, do you supply FV or PV but not both?

Thanks for your help!!

Barbara

 

[aidesifuentes]

Turns out PMT or PPMT will work. I was trying to do the whole calculation in
one formula when it really required two. Thanks for your help!

 

[Fred Smith]

You only need one function, PMT, as in:

=PMT(10%/12,5*12,500000,-1898309.51)
=13,775.90

Regards,
Fred.