Spreadsheet for calculating loan payments

[teachermom]

I just bought a "new" to me car this weekend due to some major issues on my current vehicle.

What I am looking for is a spreadsheet that someone has used that shows how much of the payment goes to interest and how much to principal each month when you are making additional payments towards the principal. I can make one that does the standard payments, but how do I do it if I want to apply extra to the principal?

I am not new to spreadsheets, so I can make a regular amortization schedule for payments. What had got me hung up is how to make it work if I make the additional principal payments.

Any suggestions would be appreciated.

Thanks!

Teachermom

[michelyn8]

To calculate the payment going to just principal on a standard amortization schedule is as simple as adjusting the formula on the line you record that payment on or just zero out the interest in that line so that your formula calcuates the full payment going to interest. (see attachment)

Attachments:

example.xls.jpg (13.5 KB)

[shanendoah]

I do things a little differently so that I have a running total.
In the attached sheets, you just need to fill in the highlighted yellow squares, and then each time you make a payment, and paste from the previous line.
This form also works for making more than one payment in a month

Attachments:

PrincipalInterest.xls.jpg (13.5 KB)

[sunuva]

I'm not certain how you have your spreadsheet set up. I tend to combine everything so that I don't have a bunch of cells, columns, or rows with intermediate steps. However, I may be able to provide some insight that helps you adjust your spreadsheet.

The one thing that you will know is your interest. For now, add a column that can handle that one thing that you know - the interest payment that month. What you don't know is the principle you are paying down (extra payments applied to principle, for example). Adjust your formulae or the look of your spreadsheet such that you remove the "known" amount from your variable amount and adjust the balance as necessary. In this way you can keep your amortization schedule formulae by pointing to the knowns (interest) and the unknowns (new balance). Think of it as instead of having one formula to handle the next 30 payments you have 30 formulas to handle just one payment. Whether you want to have those 30 formulas in 30 different cells or simply in one cell and have it be iterative (reference the last "balance") is up to you and how many intermediate steps you want to display.

[thyme4change]

My columns are:

Date - Beginning balance - Payment - Interest - Principle - End Balance

The beginning balance just picks up the end balance from the previous period.
The payment is entered (or taken from the line before)
Interest is calculated by doing beg balance x (interest rate / 12)
Principle is Payment minus interest
End balance is beginning balance minus principle

Then, you can change the payment each month and the principle will recalc.

[Clifford]

See the attached. I use this often to set early payment priorities. Fill in the purple cells. Hope this helps.

Attachments:

amorttable.xls.jpg (160 KB)

[phil5185]

new_years_ res

Nice program, thank you

[whispering17]

Do you make up your spreadsheets in excel?

[shanendoah]

whispering: all the spreadsheets that were posted here are in excel. Its a very versatile program

[teachermom]

Thanks for all the samples. New_years.....that is what I was looking for, just couldn't get the formula correct.

Teachermom

[Clifford]

No probs. Credit where due, I think I pulled that down from the templates at miscrosoft online years ago. Best of luck on the newer car...

[whispering17]

I guess I better find out how to do the calculations to set up those spread sheets!
Thanks for sharing

[TheOtherMe]

I would like to ask if anyone has a spreadsheet that considers the following. I borrowed money from a friend in 2003 at 5% interest. The start of the payments was delayed because I already owed him some money, which I was paying back. Once that was paid back, I started paying on the second loan.

I have made some advance payments and skipped payments. I have a commercial program that is showing the loan is paid off. However, we both know that is not accurate as he loaned me additional money for a car repair last spring.

Any hints on how to set up a spreadsheet to check the commercial program, which is T Value. I had used that program at a CPA office where I had worked and thought it would give me the correct answer, but it apparently isn't. I know all of my payments and the loan amounts.

TIA

[shanendoah]

taxpro: You should be able to use the spreadsheet I posted.
Put the APR in the first highlighted cell.
Put the date you borrowed the money and the amount in the next two highlighted cells.
Then put in the dates and amounts of the payment under that in columns A & B. copy columns C-E down.
If you borrowed more money at the same interest rate, just put that in on the date you borrowed it as a negative payment amount.

That's actually why my sheet is set up the way it is. It makes it very versatile.

[TheOtherMe]

Thank you. I will try that tomorrow.