# How to make spreadsheet to plan loan payments

I am trying to make a budget/schedule for after college. Basically I have rows of income/expense line items (one being for payment of each debt) and then a section for my debts. The columns are month by month.

What I would like to do is input the starting balance of my loan, as well as its interest rate. Then have a formula that takes the previous balance and updates to the new balance taking the interest rate and payment amount into account. Only thing is I am not quite sure how to do this.

Could anyone give me any tips or point me in the right direction on something to read on how to do this?

Tony, here is the line I use -

=A2*(1+(0.04375/12))-773.49

The prior month end balance appears in cell A2. 4.375% is the annual interest and \$773 is the payment. When you copy this to the cells below it will give you the new balance each month.

If your bank uses exact days, vs the same amortization each month, this will be close but at year end you'll have a dollar or two error. This is a mortgage, and the bank's interest is based on 1/12 of the year each month, so it's precise to the penny for me.

• Awesome, I thought it was going to be more complicated than that for some reason.. but that works for me! Jan 29, 2014 at 3:40
• Glad to help, truth is, for this effort, there's no need for the PV, FV, etc. Just add interest and make a payment. Welcome to Money.SE. Jan 29, 2014 at 4:09

There are a number of sites online where you can download Excel templates for free and customize them according to your needs. Or you could just use the logic and apply it to the spreadsheet that you've created.

For example, you can refer to Microsoft's Excel Templates and download a few to give you a bit of a head start. Good luck!

• +1 Welcome to Money.SE. Your link was a good one, there are many templates there that can be helpful if one doesn't want to write their own. Jan 28, 2014 at 20:19