Keeping track of loan payments and interest
 

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.

__|_____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.

Tony

Perhaps the free MS Loan Amortizer Template will suffice?

http://office.microsoft.com/en-us/te...CT011377171033


Gord Dibben Excel MVP

On Tue, 5 Jul 2005 08:36:06 -0700, "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.

Thanks Gord but that seems to cater for fixed period loans and annual
interest I'm looking for an open ended period and monthly interest based on
fluctuations with Uk Base Rate.
Any help?
Thanks again
Tony
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Tony

Perhaps the free MS Loan Amortizer Template will suffice?

http://office.microsoft.com/en-us/te...CT011377171033


Gord Dibben Excel MVP

On Tue, 5 Jul 2005 08:36:06 -0700, "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.

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.