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. We’ll 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. Here’s an example to clarify exactly what to do.
An example of calculating a capital lease interest rate
Let’s 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, let’s 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%.
Related investing topics
You're clearly an active and motivated investor. If you're interested in getting the most out of your investments, visit our broker center to find a broker that's right for your needs.
