Need help with very complicated loan spreadsheet.

[C4nt]

Hi everyone, I was wondering if anyone can help me out with this.

I have a loan and wanted to do a spreadsheet to keep an eye on
everything. I've looked everywhere for one that will suit me without any
luck.
I don't want anyone to bother making me one, just need the formula
really. Well heres what is included in the speadsheet:

I want the amount borrowed, the amount paid back and the remaining
balance with interest. Problem here is the interest is '1% per month on
the reducing balance' So if I pay it all back in one month I wouldn't
get charged any interest! I can also pay back any amount I want,
whenever I want. But how the hell do you do a formula for this?

(this isn't totally necessary but I would like it to automatically
change each month when the interest is added on the remaining balance,
wether i pay anything back or not.)


Want it to look a bit like this, so everytime I pay some money back I
add it on a new line and excel calculates the remaining balance with
interest and the total interest i've paid back.


PAID BACK\ DATE\ REMAINING BALANCE WITH INTEREST\ TOTAL INTEREST
PAID
1 £20.00 01/01/07 £200.00 £2.97
2
3
4
5
6
7
8
ETC


Any help will be great

C4nt

 

[Guest]

You might not want to do it in one formula. Consider doing it in 2 or 3
formulas which will give you more detailed information anyhow. I'll leave it
to you to set up the general formula to calculate the interest at any given
date. You have things set up like this, or so it seems

Column A = payment
Column B = date
Column C = remaining balance (after payment in A was made?)
Column D = Total Interest (over entire period or just last period?)

At any given point in time you owe some interest on the outstanding balance.
Calculate that first. Simple formula (formulas shown as they would be in
row 2)
Most likely this fits into column D
=.01*D1
Of the payment made, a piece of it goes towards the interest, the remaining
goes toward the principal. You can record the amount that went towards the
balance in Column E using
=A2-D2
for the value in C, I think it looks something like this:
=C1-A2+D2
although it might be =C1-A2+D1, depending on where you're bringing the
interest back into the amount to be used as Remaining Balance w/Interest to
be used as the basis for calculations next month.

You might want to look at some of the financial functions built into Excel
such as PMT(), FV() and PV(). Although you may not feel they are entirely
applicable to your situation.