|I am trying to determine a way to use =PMT function to solve for payment at different future values.|
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.