0

I am stuck on this problem and have only basic knowledge of accounting. Can you help me?

Trying to find the effective annual interest rate of a car lease situation. The cost of the car originally is $24,995. A lease is set up with an initial $1594 payment and $295 monthly payments for 42 months. Assume the interest is compounded monthly. The residual value of the car if you want to buy it at the end of the cycle is $14,130. How can this be done in Excel?

I have found that the total amount in interest over time will be $3119. That is, if the customer pays the $295 for 42 months and add the principal value of $1594 to that. Using that sum and finding the difference from the original sale tag of the car is how I came up with the total interest amount. How can I convert this into effective annual interest?

user3042929
  • 1
  • I did a quick Google search for "car loan excel" and was presented with several different options for downloading a template. Another search team would be "amortized loan excel". My searches were done without the quotes. You can use these templates and essentially work backwards. – BobbyScon May 02 '16 at 17:07
  • the problem I am having is that I do not have a specified APR or interest rate of any sort. All of these calculators require some sort of interest rate value. Is it possible to find the interest rate by using $3119/$24995 to find an interest rate? – user3042929 May 02 '16 at 17:21
  • Are you trying to use this to determine if a lease or a purchase is a better deal for you? And are you assuming that $14,130 is the true residual value of the car - are you assuming you will want to buy it at that point or that you would sell a purchased car at the same point roughly? – Joe May 02 '16 at 17:22
  • You could also use this article from Edmond's and set the interest rate as the variable (X), so then you just need to solve for X. http://www.edmunds.com/car-leasing/calculate-your-own-lease-payment.html – BobbyScon May 02 '16 at 17:24
  • this is purely theoretical. It is a practice assignment. – user3042929 May 02 '16 at 17:53
  • Does 3% sound reasonable for an annual interest rate for a car lease? – user3042929 May 02 '16 at 18:05

1 Answers1

1

What you can never do is try to add together payments made at different times. This defeats the whole principal of time value of money.

The trick here is to compare two car purchaser: #1 pays cash, and #2 pays the down payment, the regular lease payments, and the final buy-out amount. You need to find the interest rate to make these two payments schemes equivalent. But you must bring all payments to the same date, using the time value of money, and you need the interest rate to calculate the discounted value of the lease payments and the buy-out!

One answer is to use the "What-If" feature in Excel.

In this Excel screen grab:

enter image description here

I've entered the data from your loan along with some calculations.

The monthly interest rate is a wild guess, and the Annual rate is just that value multiplied by 12. The "PV" line is the present value (at the purchase date) of the regular payments, using that guess. The "Balance" line is the present value of the buy out discounted to 42 months earlier at that same guess rate. (Note the negative sign on the exponent).

The important value is between the two sets of chevrons (>>>>> <<<<<<<) It represents the cash value minus (the down payment plus the Present Value plus the discounted buy out)

If the guess were correct, this value should be zero. It isn't so I could guess at different values for the monthly rate. But Excel will do it for me. If I select "Data / What If Analysis / Goal Seek", I get this screen:

enter image description here

In the Goal Seek window, I selected the chevron value to be zero by changing the interest rate, and clicked "OK" the results appeared in less than a second: the difference is zero, and the interest rate is changed to 0.39% per month, or 4.39% annually.

DJohnM
  • 4,294
  • 2
  • 15
  • 19