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):

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

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.

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.

Trying to do the math for this problem. Land is leased for 50 years. Tenant pays $50,000 up front for the entire lease period. There is 44 years remaining on the lease. If the market supports a discount rate of 9%. What is the value of the remaining lease, if the tenant wants to sell it?

Robert, in order to value the remaining lease, you would need to know how much the land can be rented out for annually in today’s market. If you give me that information, I will be able to calculate the value of the lease based on your discount rate. Thank you for reading!

First you need to calculate what PAYMENT stream (annuity) gives you $50,000 PV, if discounted at 9% interest rate over 50 years. The answer is $4,561.34/-.

{Excel Formula: =PMT(9%, 50, $50,000)}

Now, suppose the investor is still expecting the same annual cash flow from the land, i.e., $4,561.34/-, so he will be willing to sell it @ $49,538.49/-.

{Excel Formula: =PV(9%, 44, $4,561.34)}

With the excel function described above, how to factor in the fact that a lease is half-way through? For example, in the example above, where there are 120 periods in total, what if there were only 85 remaining? Thank you

Julia, all you would do is adjust the number for the remaining payments. So in the formula above, your Nper would be 85. Please note that that would give you the present value of the minimum lease payments as of today (the current date).

Thank you kindly!!

If the cost of the asset is 555,091 and the finance amount is only 496,800. 58,291 is the downpayment made upfront. payment is made on the 1st day of every month at 14,870.88 @ rate of 5.2 at 36 months. Do i have to include the upfront downpayment when calculation PV?

Yes you would. All you would do is add 58,291 to the present value you calculated. The present value of 14,870.88 for 36 months at 5.2% is 496,838. You would then add the 58,291 downpayment amount to that value to get 555,129, which is the present value of the total payments. Please note that I am assuming here that the total amount paid in the first month is 73,161.88 (downpayment of 58,291 + first month’s payment of 14,870.88). Thank you for reading!

I have approximately $10K worth of high-end (super clean) fitness equipment. It is stuff that can last a lifetime if cared for accordingly. I would like to lease it to a trusted person for 2-5 years with the intention of giving them an option to buy at the end or receive the equipment for my business/personal use once again. I have changing my business model at the end of my current office lease and would like to avoid selling or merely sticking it into storage. How would I calculate a fair monthly lease payment and subsequent purchase option at the end of the lease?

Hi

I’m having a hard time trying to solve this, any help??

Lessor leases a machine having a 3-year life to Lessee for a 3-year lease period. The cost to Lessor was $20,000; the selling price is $25,000. The annual rentals begin immediately on January 1, 19A, and Lessor’s two conditions have been set.

Lessor’s target rate of return is 10% and this rate is known to Lessee. However Lessee’s own incremental rate is 12%.

Hi

How do I calculate the PV of MLP if there is a residual value on the asset?

Thank you!

Nicola, you would include any guaranteed residual values as part of your minimum lease payments. So if there is a guaranteed residual value of $1,000, then you would include $1,000 to be paid at the end of the lease as part of your MLP.

How do you determine what rate to apply? The commercial lease I’m trying to calculate the present value of is for the rental of a truck repair bay, not an asset that would be bought (or sold) outright like a piece of machinery.

Virginia, 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.