Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
__|_____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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate correct interest on loan when debtor pays late | Excel Discussion (Misc queries) | |||
Extra Payments go toward principal or interest in template? | Excel Discussion (Misc queries) | |||
Formula for calculating interest payments? | Excel Worksheet Functions | |||
Calculating credit card debt, interest , and payments | Excel Worksheet Functions | |||
How to calculate total interest on 12 month loan with early payments | Excel Worksheet Functions |