Microsoft excel template, or spreadsheet that calculates interest and balance?

Question

I lent somebody certain amount of money (lets say $2000) using my credit card. That person makes me payments every week, or every two other weeks, but the payment amount changes everytime, and its never paid on the same date.
I would like to keep track of the payments, and the running balance of the debt, since I know that the interest rate is charged every month to my credit card. I can do it manually multiplying the balance by the apr and dividing it by 12months, and then substracting whatever money was paid, but I would like to know if I can have this on a template. thanks!

Answer 1

Here's a nifty excel calculation, it's a function, if you're not familiar with functions, this may be difficult:

In order to do it you'll need to know how many months he's going to need to pay it off. Then if he's paing monthly, you use the function:
=PMT(Rate, Nper, Pv)
For Rate you select the cell that the annual interest rate is in, divided by 12. For Nper you enter the cell that holds the number of payment periods (how many months), then Pv is the cell that holds the present value fo the loan (but you have to make this negative).

So, put your loan amount in B3, your annual interest rate in B4, and your months to pay it off in B5. The choose a cell and enter the folowing formula:
=PMT(B4/12, B5, -B3)

I know this is a little much, but if you just enter things exactly like what I've given you above, that will calculate what their monthly pament should be.

Good luck.

Answer 2

you have to make an amortization schedule in excel. There is a template. Its based on 12 months though, it will take some manipulation but you can handle it. Play around with it, like set one up for your car payment and see what happens when you pay a little more. When you are comfortable with that set up one for the loan. Its works great but its not under any expense template its under "Loan Amortization"

Answer 3

You can download what you need from the Microsoft web site, just look for the section for MS Office dowloads o go to the site a do a seach using Excel Templates

Answer 4

Sure you can, use the ammortization template with Excel. Should be under "File" New, then Speadsheet solutions. May be under expense sheet.