What is a formula for calculating equity accumulated while repaying car loan?

Question

I'm going to buy a used car (maybe a Honda CR-Z), pay for it with a bank loan, then resell it in two years when I move. I'm trying to create a Google Docs spreadsheet to figure out how much more money I'll lose if I buy a more expensive car (let's say $20,000) than a cheaper car ($12,000).

So let's say I buy a car for C dollars, pay 10% down, and finance the rest at an interest rate of i. Let's say that whether I buy a cheap car or an expensive car, either way, I can afford to pay $400 a month in auto payments. I'm assuming the term of the loan though would be maybe 48 months, or whatever it works out to be such that I don't pay much more than $400 a month.

Then, in 24 months, I resell the car. I'm curious to know how much more money I'd lose with the expensive car than I would with the cheaper car, assuming no depreciation. More importantly, I'd like to figure out how much more money I'd lose assuming normal depreciation.

What's the formula to figure out how much equity I will have when I sell the car after 24 months of $400 payments, given the accumulating interest from the loan? This seems like a complicated compound interest problem, if I understand it correctly. Which I may not.

What's the formula and how should I enter it into Excel/Google Docs?

Answer 1

By the sounds of things, you're not asking for a single formula but how to do the analysis... And for the record you're focusing on the wrong thing. You should be focusing on how much it costs to own your car during that time period, not your total equity.

---

Formulas: I'm not sure how well you understand the nuts and bolts of the finance behind your question, (you may just be a pro and really want a consolidated equation to do this in one go.) So at the risk of over-specifying, I'll err on the side of starting at the very beginning. Any financial loan analysis is built on 5 items: (1) # of periods, (2) Present Value, (3) Future Value, (4) Payments, and (5) interest rate.

These are usually referred to in spreadsheet software as NPER, PV, FV, PMT, and Rate. Each one has its own Excel/google docs function where you can calculate one as a function of the other 4. I'll use those going forward and spare you the 'real math' equations.

Layout: If I were trying to solve your problem I would start by setting up the spreadsheet up with column A as "Period". I would put this label in cell A2 and then starting from cell A3 as "0" and going to "N". 5 year loans will give you the highest purchase value w lowest payments, so n=60 months... but you also said 48 months so do whatever you want.

Then I would set up two tables side-by-side with 7 columns each. (Yes, seven.) Starting in C2, label the cells/columns as: "Rate", "Car Value", "Loan Balance", "Payment", "Paid to Interest", "Principal", and "Accumulated Equity". Then select and copy cells C2:I2 as the next set of column headers beginning in K2. (I usually skip a column to leave space because I'm OCD like that :) )

Numbers: Now you need to set up your initial set of numbers for each table. We'll do the older car in the left hand table and the newer one on the right. Let's say your rate is 5% APR. Put that in cell C1 (not C3). Then in cell C3 type =C$1/12. Car Value $12,000 in Cell D3. Then type "Down Payment" in cell E1 and put 10% in cell D1. And last, in cell E3 put the formula =D3*(1-D$1).

This should leave you with a value for the first month in the Rate, Car Value, and Loan Balance columns. Now select C1:E3 and paste those to the right hand table. The only thing you will need to change is the "Car Value" to $20,000.

As a check, you should have .0042 / 12,000 / 10,800 on the left and then .0042 / 20,000 / 18,000 on the right.

Formulas again: This is where spreadsheets become amazing. If we set up the right formulas, you can copy and paste them and do this very complicated analysis very quickly.

Payment The excel formula for Payment is =PMT(Rate, NPER, PV, FV). FV is usually zero. So in cell F3, type the formula =PMT(C3, 60, E3, 0). Obviously if you're really doing a 48 month (4 year) loan then you'll need to change the 60 to 48. You should be able to copy the result from cell F3 to N3 and the formula will update itself.

For the 60 months, I'm showing the 12K car/10.8K loan has a pmt of $203.81. The 20K/18K loan has a pmt of 339.68.

Interest The easiest way to calculate the interest is as =E3*C3. That's (Outstanding Loan Balance) x (Periodic Interest Rate). Put this in cell G4, since you don't actually owe any interest at Period 0.

Principal If you pay PMT each month and X goes to interest, then the amount to principal is "PMT - X". So in H4 type =-F3 - G3. The 'minus' in front of F3 is because excel's PMT function returns a negative amount. If you want to, feel free to type "=-PMT(...)" for the formula that's actually in F3. It's your call.

I get 159 for the amount to principal in period 1.

Accumulated Equity As I mentioned in the comment, your "Equity" comes from your initial Loan-to-Value and the accumulated principal payments. So the formula in this cell should reflect that. There are a variety of ways to do this... the easiest is just to compare your car's expected value to your loan balance every time.

In cell I3, type =(D3-E3). That's your initial equity in the car before making any payments. Copy that cell and paste it to I4. You'll see it updates to =(D4-E3) automatically. (Right now that is zero... those cells are empty, but we're getting there) The important thing is that as JB King pointed out, your equity is a function of accumulated principal AND equity, which depreciates. This approach handles those both.

Finishing up the copy-and-paste formulas I know this is long, but we're almost done.

Rate // Period 1 In cell C4 type =C3.

Payment // Period 1 In cell F4 type =F3.

Loan Balance // Period 1 In cell E4 type =E3-H4. Your loan balance at the end of period is reduced by the principal you paid. I get 10,641.

Car Value // Period 1 This will vary depending on how you want to handle depreciation. If you ignore it, you're making a major error and it's not worth doing this entire analysis... just buy the prettiest car and move on with life. But you also don't have to get it scientifically accurate. Go to someplace like edmunds.com and look up a ballpark. I'm using 4% depreciation per year for the old (12K) car and 7% for the newer car. However, I pulled those out of my ass so figure out what's a better ballpark.

In G1 type "Depreciation" and then put 4% in H1. In O1 type "Depreciation" and then 7% in P1. Now, in cell D4, put the formula =D3 * (1-(H$1/12)).

Paste formulas to flesh out table

As a check, your row 4 should read 1 / .0042 / 11,960 / 10,641 / 203.81 / 45 / 159 / 1,319. If so, you're great. Copy cells C4:I4 and paste them into K4:Q4.

These will update to be .0042 / 19,883 / 17,735 / 339.68 / 75 / 265 / 2,148.

If you've got that, then copy C4:Q4 and paste it to C5:C63. You've built a full amortization table for your two hypothetical loans. Congratulations.

---

Making your decision I'm not going to tell you what to decide, but I'll give you a better idea of what to look at. I would personally make the decision based on total cost to own during that time period, plus a bit of "x-factor" for which car I really liked.

Look at Period 24, in columns I and Q. These are your 'equities' in each car. If you built the sheet using my made-up numbers, then you get "Old Car Equity" as 4,276. "New Car Equity" is 6,046. If you're only looking at most equity, you might make a poor financial decision.

The real value you should consider is the cost to own the car (not necessarily operate it) during that time... Total Cost = (Ending Equity) - (Payment x 24) - (Upfront Cash).

For your 'old' car, that's (4,276) - (203.81 * 24) - (1,200) = -1,815.75

For the 'new' car, that's (6,046) - (339.68 * 24) - (2,000) = -4,106.07.

Is one good or bad? Up to you to decide. There are excel formulas like "CUMPRINC" that can consolidate some of the table mechanics, but I assumed that if you're here asking you would have gotten stuck running some of those.

Here's the spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0Ah0weE0QX65vdHpCNVpwUzlfYjlTY2VrNllXOS1CWUE#gid=1

Answer 2

Here is a simple way to analyze the situation.

Go to your bank or credit union website and use their loan calculator with their current real interest rates and down payment requirements. Enter the rate, and number of years. Enter different values for the loan amount to get the monthly payment to the level you want ($400).

Today for my credit union, the max loan would be about $9,500. Keep in mind there may be taxes, registration fees, and down payment on top of this.

Jump ahead two years. The loan is paid off, the car is owned free and clear. You will be able to sell it and get some money in your pocket.

If you go for a longer term loan to keep the payments under your goal the issue is that in two years you might be upside down on the loan. The car may be worth less than the remaining balance on the loan. Your equity would be negative.