Need a spread sheet showing a debt with repayments and interest please.

pampam

Hope someone can help me with this please.

I loaned my grandson some money to help him buy a house.
To do this I had to cash in my bonds and empty my savings account.
He hasn't made much attempt to pay me back. I have now put my foot down and he has set up a standing order to pay me monthly.
I want to charge a small interest amount to the loan to recoup the interest I have lost. 
Can someone tell me where I can download a free spread sheet, please?
I need it to show the payments, and also automatically add the interest each month and show the balance if possible.
I hope this make sense?.
Many thanks
Pam

Brie

This will work loosely

First row
Excel spreadsheet with 4 columns - A- total, B - interest, C - paid, D - owed

Second row
Put the total amount to paid under total (cell A2)
Put the interest being charged under interest (cell B2)
Put the amount to be paid each month under paid (cell C2)
And the owed column will show you what is outstanding.  (D2)

Third row type in the following
Cell A3  "=A2"
Cell B3 "=A2*$B$2%/12"
Cell C3 "=$C$2"
Cell D3 "=A3+B3-C3"

Fourth row
A4 "=D3"
Cell B4 "=A2*$B$2%/12"  (identical to row above)
Cell C4 "=$C$2"             (identical to row above)
Cell D4 "=A4+B4-C4"      (identical to row above)

After that you should be able to drag/copy the rows down.

You can then put sum totals at the bottom of columns B & C to show how much total interest you are charging over time and how much the payments have been over time.

If grandson misses a payment or pays more or less you just need to adjust the payment for that month.

Sorry - which reminds me you will want a date column as well - this could be inserted before or after the columns as noted above.



Happy to have other point out where I've messed this up - I know there are easier ways to calculate interest but this works nicely for me to give a vague answer rather than something expected from a bank.

pampam

Thank You very much Brie
I will give this a try in the morning.

pampam

pampam said:

Thank You very much Brie
I will give this a try in the morning.

Unfortunatly, this didn't work but thank you for trying to help 

ididgetwhereiamtoday

You could try an online loan calculator. You can adjust the interest rates and length accordingly. 

uknick

pampam said:

pampam said:

Thank You very much Brie
I will give this a try in the morning.

Unfortunatly, this didn't work but thank you for trying to help 

I've just tried the example given and it works for me giving a reduced balance outstanding each period to apply the interest rate to.

What didn't work for you; type of interest (compound or simple) or what balance the the interest is charged on?