How to Calculate Capital Lease Interest Rates

Sometimes a company will elect to lease a fixed asset instead of purchasing it outright. This could be driven by attractive lease financing terms, balance sheet management, or other reasons. If a company does finance an asset with a capital lease, we can calculate the effective interest rate on the lease with a simple spreadsheet formula if we know the amount financed and the payment amount. We'll assume there's no residual value or buyout of the asset for simplicity in this explanation.

Step 1: Set up your spreadsheet

To do this calculation, we'll use a spreadsheet to make the calculation as easy as possible. First, we'll set up the data.

In a column in the spreadsheet, input the amount financed in the top cell. In the cell below that, input the first payment amount as a negative number. The amount financed is positive because the company is receiving the asset. The regular payments will be input as negatives because the company is paying out that cash.

Continue entering the payment amounts as negative numbers for all of the scheduled payments.

Step 2: Using the internal rate of return function to calculate a capital lease interest rate

If the payments are made annually, the next step is straightforward. Use the =IRR() function in your spreadsheet to calculate the interest rate of the capital lease. Select the payment data you set up in step one, beginning with the amount financed and ending with the final payment amount. Well demonstrate with an example in the next section to clarify exactly what this looks like.

If the payments are paid monthly or quarterly, our formula is slightly different to adjust for the non-annual payment schedule. The internal rate of return calculation is by definition annual, so we just have to tweak the calculation a bit to account for that.

For a monthly payment schedule, use this formula: =(IRR()+1)^12-1.

For a quarterly payment schedule, use this formula: =(IRR()+1)^4-1.

Taking a closer look at each formula, you can see that we're inserting the number of payments per year in the exponent, effectively adjusting the internal rate of return calculation to work for each respective non-annual payment schedule. Heres an example to clarify exactly what to do.

An example of calculating a capital lease interest rate

Lets assume that a company is leasing a vehicle. The company is financing $19,000 and will make annual payments of $6,000 for four years. To calculate the interest rate on this lease, we just set up our spreadsheet with the $19,000 loan amount as a positive number, each of the four annual payments (as negatives), and then calculate the internal rate of return using the built-in spreadsheet function. Your spreadsheet should look like this (the arrow is pointing to the formula for demonstrative purposes), with a calculated interest rate of 10.05%.

To show how the calculation works for a lease with monthly payments, lets assume a company is financing a $9,000 computer system with a two-year lease. The monthly payments are $400. Again, we set up our spreadsheet with each payment and the financed amount, but this time, we use the formula from above that adjusts the interest rate for monthly payments instead of annual. Once completed, your spreadsheet should look like this and give you an interest rate of 6.46%.

This article is part of The Motley Fool's Knowledge Center, which was created based on the collected wisdom of a fantastic community of investors. We'd love to hear your questions, thoughts, and opinions on the Knowledge Center in general, or this page in particular. Your input will help us help the world invest, better! Email us at knowledgecenter@fool.com. Thanks -- and Fool on!

The article How to Calculate Capital Lease Interest Rates originally appeared on Fool.com.

Try any of our Foolish newsletter services free for 30 days. We Fools may not all hold the same opinions, but we all believe that considering a diverse range of insights makes us better investors. The Motley Fool has a disclosure policy.