Thanks for that but I'm not sure that fits in with what I'm trying to do. I
have fixed payments £250 per month, interest geared to UK bank Rate charged
monthly at say 1% over bank rate and an open ended period. I'm not sure how
what you've suggested fits in with that. I apologise if I'm missing
something but Excel is not my strong point.
Thanks
Tony
"FirstVette52" <(My User name is Firstvette 52, too) firstvet52@(my ISP
E-mail provider is) netzero.com wrote in message
...
__|_____A____________|____B______|________C______| ___D____|
_1| James Roberts, Loan Summary
_2| Outstanding: £9,855.00 Monthly Payment £266.74
_3|
_4| James Roberts, Loan 3/15/2005
_5| Price £9,125.00 Rate 8.75%
_6| Down Payment £220.00 Years 5
_7| Loan Amount £8,905.00 Monthly Payment £183.77
_8| James Roberts, Loan 6/4/2005
_9| Price £250.00 Rate 8.75%
10| Down Payment £25.00 Years 1
11| Loan Amount £225.00 Monthly Payment £19.65
12| James Roberts, Loan 3/15/2005
13| Price £750.00 Rate 8.75%
14| Down Payment £25.00 Years 1
15| Loan Amount £725.00 Monthly Payment £63.32
...would use the following formulas:
James Roberts, Loan Summary
Outstanding: =B7+B11+B15 Monthly Payment =D7+D11+D15
James Roberts, Loan 3/15/2005
Price 9125 Rate 0.0875
Down Payment 220 Years 5
Loan Amount =B5-B6 Monthly Payment =PMT(D5/12,D6*12,-B7)
James Roberts, Loan 6/4/2005
Price 250 Rate 0.0875
Down Payment 25 Years 1
Loan Amount =B9-B10 Monthly Payment =PMT(D9/12,D10*12,-B11)
James Roberts, Loan 3/15/2005
Price 750 Rate 0.0875
Down Payment 25 Years 1
Loan Amount =B13-B14 Monthly Payment =PMT(D13/12,D14*12,-B15)
For interest and "Aging" calculations, use a TABLE to build an
amortization
schedule, and flag the criteria you wish to track.
--
FirstVette52
"Tony Williams" wrote:
I suspect this is easy but I am no expert in Excel so need your help.
I want to create a spreadsheet that keeps track of loan amounts I am
making
and calculate the interest that is accruing. I want to charge interest at
1%
over UK bank rate and charge the interest on the balace outstanding at
the
end of every month. So if I lend say £250 to someone on the first of
every
month indefinately, how do I set up a spread sheet that calculates the
interest at the end of every month. Also I may lend additional amounts
during
the month which need to be added to the balance outstanding.
|