I am trying to determine a way to use =PMT function to solve for payment at different future values. To expand

I want to write a series of 12 month term leases. They will each have the same pAyment and the interest will remain level throughout each series of terms (i.e. $100,000.00 Present value, 5% interest $76,000.00 Future vale all over a 12 month term ) This will create a payment of $2371.25 and the math says it's a 2%/month depreciation (100,000 x 2% x 12 months = 76000.00)

Now I start the next 12 month term with a Present Value of $76000.00 and if I solve for a level payment (remember it's $2371.25 I get a new Future value of $50772.11 and this equates to a monthly depreciation of 2.7662%/month (76000 x 2.7662 x 12 = 50772.11

and so on.

Now the problem. I would like to have a formula that automatically changes the monthly depreciation to solve for a level payment when the annual interst % changes. (Now if I change interest my carefully calculated depreciations are all wrong)

I would like to create a spreadsheet where I can state the opening present value, annual interest, first year Future value and have it solve for a level payment for months 1 - 12 ;13- 24 ; 25 - 36; 36 - 48; 49 - 60

Essentially 5 seperate leases with level payments.Any ideas?

Not sure if this will help, but have you looked at the Solver Add-in under the Tools menu? If it's not there now, Click on Tools...Add-ins and add the Solver.

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History