How to use Excel to Calculate the Present Value of Minimum Lease Payments when the payment amount does not change.

Reader Request: This post explains how to use excel to calculate the present value of minimum lease payments when each payment is equal.  To learn how to calculate the present value of lease payments when payments change each period, click here. To calculate the present value for lease payments when the payment amounts do not change, 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.

 

This entry was posted in Minimum Lease Payments Using Excel, Present Value of Minimum Lease Payments, SFAS 13 Testing. Bookmark the permalink.

15 Responses to How to use Excel to Calculate the Present Value of Minimum Lease Payments when the payment amount does not change.

  1. Robert Felderman says:

    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?

    • George says:

      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!

    • saeed says:

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

  2. Julia says:

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

  3. George Azih says:

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

  4. slk says:

    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?

    • George Azih says:

      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!

  5. John Welch says:

    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?

  6. Karima says:

    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%.

  7. Nicola says:

    Hi

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

    Thank you!

    • George Azih says:

      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.

  8. Virginia says:

    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.

    • George Azih says:

      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.

  9. Lanny says:

    I’m having trouble figuring out the PV of minimum lease payments in this situation:
    5 years of monthly payments, total monthly payment: $11,433, interest rate if we had borrowed money from our bank: 5%
    Equipment purchase price: $632,3745; however, opening lease balance is 578,880, so I’m not sure if that means they assume the asset has a value of $53,494 at the end of the lease. I need to figure out whether this loan means the capital lease criteria of PV of min lease pmt is >90% of leased property’s fair value.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>