This post by George Azih explains how to use excel to calculate the present value of minimum lease payments when each payment is different. To see how to use excel to calculate the present value of lease payments when the payment amounts do not change, click here.
Example:
Let’s calculate the present value of the minimum lease payments for a lease with a lease term of 24 months. The lease payment starts at 5,000/month, and increases by 1,000 every 6 months. Payments are made at the beginning of the month. The Lessee’s borrowing rate is 6%.
The following embedded excel file shows you how to do this easily. (You can actually see my formulas):
From the file above (scroll down in the embedded file), you can see that the present value is $146,543.92.
Here is a manual explanation: Perform the following steps:
1) Create a table with three Headers, namely, “Period”, “Payment” and “Present Value” as follows:
Period 
Payment 
Present Value 
2) In each row under “Period” put in the number of Periods. Start with “1” if payments are made at the end of the month, and end at the actual number of periods. If payments are made at the beginning of the month, start with “0”, and end at the actual number of periods minus 1. As a result, our Table now looks like this:
Period 
Payment 
Present Value 
0 

1 

2 

3 

4 

5 

6 

7 

8 

9 

10 

11 

12 

13 

14 

15 

16 

17 

18 

19 

20 

21 

22 

23 

Total Present Value 
Note that in this example, because the payments are made at the beginning of the month, the “Period” column begins with 0 and ends with 23. If payments were made at the end of the month, it would have begun with 1 and ended with 24.
3) In each row under “Payment,” put the payment for that month. Remember that our payment starts at $5,000/month, and every 6 months the monthly payment increases by $1,000. So our table now looks like this:
Period 
Payment 
Present Value 
0 
5,000 

1 
5,000 

2 
5,000 

3 
5,000 

4 
5,000 

5 
5,000 

6 
6,000 

7 
6,000 

8 
6,000 

9 
6,000 

10 
6,000 

11 
6,000 

12 
7,000 

13 
7,000 

14 
7,000 

15 
7,000 

16 
7,000 

17 
7,000 

18 
8,000 

19 
8,000 

20 
8,000 

21 
8,000 

22 
8,000 

23 
8,000 

Total Present Value 
To get the values for “Present Value,” we have to use the PV function in excel. We will put the formula in for the first cell (period 0), then drag the formula down. Go to the first cell under “Present Value” and click the function button. Choose “PV,” (It should be under financial):
When you click “OK, you should see the following:
Enter the following:
Rate = 6%/12 = 0.5% = 0.005 (in excel, you can either enter 0.5% or 0.005 without the percentage sign)
Nper = (Select the corresponding “Period cell” for that Present Value. So if your first “Present Value” cell is “C2,” then Nper should be “A2”)
Pmt = 0
FV = (Select the negative of the corresponding “Payment cell” for that Present Value. So if your first “Present Value” cell is “C2,” then Fv should be “B2”)
Type = 0
Your screen should look like this:
Using this information, excel should give us a value of $5,000 for the first “Present Value”.
Now copy the formula down for the rest of the “Present Values,” and for the “Total Present Value,” add all the values in the “Present Values” column. Your table should now look like this:
Period 
Payment 
Present Value 
0 
5,000 
5,000.00 
1 
5,000 
4,975.12 
2 
5,000 
4,950.37 
3 
5,000 
4,925.74 
4 
5,000 
4,901.24 
5 
5,000 
4,876.85 
6 
6,000 
5,823.11 
7 
6,000 
5,794.14 
8 
6,000 
5,765.31 
9 
6,000 
5,736.63 
10 
6,000 
5,708.09 
11 
6,000 
5,679.69 
12 
7,000 
6,593.34 
13 
7,000 
6,560.53 
14 
7,000 
6,527.90 
15 
7,000 
6,495.42 
16 
7,000 
6,463.10 
17 
7,000 
6,430.95 
18 
8,000 
7,313.09 
19 
8,000 
7,276.71 
20 
8,000 
7,240.50 
21 
8,000 
7,204.48 
22 
8,000 
7,168.64 
23 
8,000 
7,132.97 
Total Present Value 
146,543.92 
From the table, we can see that the total present value of the minimum lease payments is 146,543.92.