How to Use Excel to Calculate The Present Value of Minimum Lease Payments When Each Payment is Equal

George Azih Accounting in Excel

Reader Request: Some readers have requested that I put up a blog explaining how to use excel to calculate the present value of minimum lease payments, under current accounting rules. I am happy to do so. This post explains how to use excel to calculate the present value of minimum lease payments when each payment is equal.  To do this in excel, perform the following steps:

Note: This method only works when every lease payment amount is the same each period. To see how to calculate the present value of minimum lease payments when one or more payments are different (or when there are some months with free rent), click here.

Go to a cell in excel, and click the function button.  Choose “PV,” (It should be under financial):

Lease accounting in excel

When you click “OK, you should see the following:

more lease accounting in excel

Let “Rate” be your interest rate (this is usually the interest rate which the company would get charged if the company went to a bank to borrow the money to buy the asset instead of leasing it).

Please note that this rate depends on your payment frequency. If the payments are made monthly, then you would have to divide your annual rate by 12.

NPer is the number of payment periods. So if the Payments are made monthly and the lease is over five years, then the number of NPer is 60.

Pmt is simply your payment amount.

Fv should be zero.

Type should be “1” if payments are made at the beginning of the period, or 0 if payments are made at the end of the period.

capital vs operating lease accounting tool

Example:

Assume we have a 10 year lease with monthly payments of $2,500/month, paid in advance (at the beginning of each month). Assume that the lessee’s borrowing rate is 6%. To use excel to calculate this:

Rate = 6%/12 = 0.5% = 0.005 (in excel, you can either enter 0.5% or 0.005 without the percentage sign)

Nper = 120

Pmt = 2500

FV = 0

Type = 1 (Because payments are made in advance, at the beginning of each month).

Using this information, excel should give us a present value of $226,309.55 for the lease.

Here is a free tool you can use to determine if your lease is a Capital or Operating Lease. It goes though the 4 tests for capital leases. To access the test (for free), click here.

Alternatively, if you need to comply with FASB Topic 842 or IFRS 16, you can use LeaseQuery's lease accounting software, making the transition to the new lease accounting standards nearly effortless (you'll still have to click a few times).

Ask An Expert!