Keeping track of loan payments and interest

[Tony Williams]

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.

[FirstVette52]

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

[Gord Dibben]

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"
<[email protected]> 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 Williams]

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
news:[email protected]...
> 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"
> <[email protected]> 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 Williams]

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
news:[email protected]...
> __|_____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.