
Compound Interest And Repayments On Personal Loan


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Compound Interest And Repayments On Personal Loan  Excel

View Answers


To anyone out there!!!
This is a little complicated (and maybe a bit personal), but I need help in working out formulas for an excel worksheet.
I have a personal loan of $12,000 (at an interest rate of 10.4% per annum) which my partner is paying out. However, I have nominated my bank to deduct $800 per fortnight to finance this loan and my partner pays me whenever he can.
I would like to keep track of everything  the balance, interest, my repayments, and what my partner owes me.
I know how to set up the first 4 columns for the date/balance/interest/my repayments, but it doesn't coincide with my bank statments. I think this is because interest is calculated daily but added monthly. Is there a formula to relate to this, rather than the interest added to the principle on a daily basis?
Also, I'd like to work out how much my partner owes me. For example, if I pay $800 fortnightly and he gives me $500 in the first week and another $500 in the second week, then that means he owes me a total of $600. Is there a formula to add this automatically rather than me working it out all the time?
Please help!!!
My experience is only average with excel, so please reply in laymen terms
Thanks!!!
Similar Excel Video Tutorials
Simple & Compound Interest
 See how to use the FV function. See the math formula for calculating Future Value and for calculating the effective interest rate. Also see long hand ...
Personal Budgets and VLOOKUP
 See how to create a personal budget based on past data, contract numbers, the SUM function, cell references, subtraction and the VLOOKUP function. For ...
Similar Topics
I'm trying to work out how much interest I have lost by investing an
amount into a project.
So, I'd have to use the following information which would be in my
excel worksheet:
date of loan: 25/4/2006
amount of loan 30000
interest rate potentialy earned at bank: 6%
todays date: 20/6/2006
Excel should then work out how much interest has been lost by not
having the money invested in a bank given the time elapsed between the
date of loan and todays date. To obtain an accurate figure given
compound interest I'd also need to tell it how often interest is paid 
daily, monthy etc. So maybe another cell.
interest interval : 12 (12 times per year for monthly)
I've tried the financial funtions but they don't seem to quite fit  or
maybe I'm not doing it correctly. Could anyone guide me to a formula
that would help?
Thanks,
Mark.
Hi all,
This should be straightforward to find through help, but I'm struggling!
Basically I want to caculate the total cost of a loan, but not sure which function I should be using. PMT seems like the closest one, but it doesn't give the right answer. Basically the loan model is as follows shown as an example.
A loan for $210 is taken for 6 months at 2.5% per/month (an expensive loan!).
The repayments are made monthly and are made up of 2 parts  capital payments and interest payments.
Capital repayments:
Month 0  $0, Mth 1  $35, Mth 2  $35, Mth 3  $35, Mth 3  $35, Mth 4  $35, Mth 5  $35, Mth 6  $35.
Interest payments:
M0  $0, M1  $5.25, M2  $4.38, M3  $3.5, M4  $2.63, M5  $1.75, M6  $0.88
So as you can see the capital repayments are fixed while the interest payments are diminishing since the capital repayments are bringing down the remaining amount.
So I need the simplest loan formula that will give me the $18.38 result (i.e. sum of all the interest payments).
Thanks in advance
Anar
I need to create a formula to work out the comparison rate, ie The current interest rate on a $15000 loan is 9.7%, 4 year term and repayments of $88/week. The comparison rate is trying to work out the real interest rate including fees and charges which for this example would add $10 to the loan amount. All calculated on weekly repayments
I have tried to use the rate function but have confused myself to date.
nper  does that mean 208
pmt  I have put in 10010
guess  9.7%
do I still have to divide by twelve or should it be 52 if I am working on a weekly basis???
Any help would be great!!!
Is there a formula for calculating interest on a Personal line of credit?
(there is no amortization dateit is an ongoing loan). Currently I owe
$5,342.62 and interest rate is 5% (says calculated on a daily basis). My
interest on last statement was $20.93 (28 day period, with a $200.00
payment). I am adding on $15,000 next month and want to be able to determine
when my original is all paid off.
Thanks for help.....
I'm curious if anyone has seen a spreadsheet for a personal loan?
All I've been able to find is car and home loans which have a fixed period for the loan and all have regular payments. The payee is not making regular payments so I'm computing interest daily. When a payment comes in, that's taken off the balance and we just continue charging interest each day on the new amount.
I'd like to show the current balance, any payments, the principle amount, the interested charged, and daily charge on the balance.
I'm hoping that this will allow them to see how fast the interest is racking up, and how there payments do make a difference.
Any help would be awesome!
Hey all.. I have a calculation I do that calculates a clients "effective interest rate" if they make extra payments towards principal.. Calculation works fine.. However, I am now trying to figure out how to amend that code if it's an interest only loan, anyone have any ideas?
Here is the effective rate calcs on a random normal amortization loan:
Code:
this is in B2, and answer is 7%
=RATE(B4*B5,((B3+B7)/B6),B7)*12
B3 = Total*Interest 279017.8
B4 = #*Years*in*Loan 30
B5 = #*Payments*/*Year 12
B6 = Total*Payments 360
B7 = Beginning*Principal 200000
B8  Ending*Balance 0
problem is when someone is on an interest only loan they pay more interest than a normal amortization because they are not reducing the principal in the first x number of years. So I need to compare the interest only effective rate to an interest only loan and I have no clue how to do that... hoping I'm clear..
Here is the example I'm working on... A client's loan is the following:
Loan amount  131,538
interest rate  6.15
30 year amortization
10 years interest only
normal client would pay an interest only payment of 674.13, then after i/o period would go to 953.80 for last 20 years of the loan, and they'd pay about $178k in interest.. Now if that client pays an extra 1,000 per year, I can calculate the amount of interest they'd accrue, but have no clue how to back into the "effective interest rate", basically that says you are paying the same amount of interest as someone with a x.xx% interest only loan. Geez I hope this makes sense.. LOL.. Thanks for any help!
John
hi....wasnt sure what this function is called and hence couldnt search for it prior to this thread....sorry..
I have an excel file with 2 worksheets in it viz. Summary and Details
Details is a simple worksheet that calculates outstanding balance on the loan on any given day.
It has 8 columns : Month, Day (mon, tue, wed...etc), Date, Loan Amount, Daily interest, Interest Rate, Money In, Money Out. So basically it just looks like a very basic bank statement that shows loan balance on a daily basis and works out compouding interest on a daily basis and adds it to the next days principal amount while accounting for any other money in/out transactions. This table has one row for each day till Dec 09.
Summary worksheet just shows the summary of the (projected) principal balance at the end of each month, Interest paid/acrued each month, total money in and total money out.
I am trying to have cell on summary page that will say "New Interest Rate" and want to be able to transfer that interest rate value to all the cells in the "Interest Rate" column in the Details worksheet, but only in the ones from todays date on onwards (without changing the interest rate for the past dates)
is it possible? help would be really appreciated. Merry Christmas everyone.
I am trying to construct a table where a loan (say $500,000) is 50% amortised over a given period (say 5 years) at a fixed interest rate. I can't work out the formula which will allow it to calculate interest on the full $500k in the monthly repayments but only make capital repayments on $250k
I am trying to use PMT. Is that suitable?
Thanks for any advice.
Hi all
I am wondering if somebody may be able to help me with the PMT function.
I need to calculate the yearly repayment based on the following information:
 Loan principal $843,500
 Interest rate is 7%
 Interest accrues monthly in arrears
 Repayment occurs at end of year
 Loan term 7 years
So basically there is only 7 repayments, however interest is compounded monthly.
I have worked out interest for the first year using the following formula in excel:
=(843,500*(1+0.07/12)^12)843,500
Help is much appreciated.
Thanks
Phil
Hi,
I am trying to calculate the interest rate required for a loan of 200,000 with 60 monthly repayments of 6,500 to leave a residual of 60,000.
The rate is approximately 4.88%, calculated by trial and error in a 60 row worksheet, but is there a formula to calculate this figure direct?
Thanks in advance.
Bryan
Hi folks,
I need to work out how to calculate the interest income from loans in relation to when they are completed during a calendar year. For example if $100,000 is charged at 3% then for a full year it would pay $3,000 but if it was only completed in June it would only pay $1500 in a calendar year.
Loan Types 1 to 5, Loan amount in $, the interest rate, and the loan status (i.e. Completed or Pending) are all listed in separate columns.
I want to calculate the interest income for each Type of loan that will be paid in the year but only when the status says Completed.
Loan interest is paid monthly on a fixed loan balance so keep things simple the interest on a loan that completed in February would be 11/12ths of yearly interest and if it completed in June it would be 1/2 of the yearly interest.
I do not need really to work out interest for the exact number of days (though that would be in 'interesting' proposition in Excel!!!)
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.
I need to know if there is a formula for Excel to calculate the future value using daily compounded interest for a loan. the loan amount is $1,056,999, the interest rate is 5.25% and the loan term is for 2 years.
HI EVERY FRIENDS. PLEASE HELP ME. I LOAN FROM BANK AND THIS SYSTEM IS TOTALLY EASY BUT I DON'T PUT FORMULA IN EXCEL.
THIS SYSTEM IS BELLOW MY REQURED:
LOAN AMOUNT 250000
Annual Interest Rate 12%
Loan Period in Years 5
Number of Payments Per Year12
Start Date1122009
Payment (per period).................
Number of Payments...................
Total Interest Paid...................
Total Interest.......................
Total Payment........................
Payment Date:112010
Payment: pRINCIPLE+iNTEREST
Principal: This loan system is principle fixed.(i.e. Rs.250000 loan amount and my term 5year and 12 per year so my principle money is Rs.4166.667[fixed] and first month deducted this principle money from loan money i.e. 2500004167(round figure)=Rs.2,45,833)
Interest: interest calculate first month Rs.250000, second month 245833, third  241667, 4th, 5th ....60th.
Balance:loan amountprinciple money
please help me for formula. please say to me which formula use this system and i get totally 60 or more period all are together.please don't ignore me. i wait ur formula.
Hi there,
I'm having a few problems in preparing a loan amortisation schedule without using a goal seek function that will calculate principal and interest, where the interest is calculated on an Actual/360 day basis and the principal and interest payment each month is kept reasonable constant (it can move slightly depending on the numbers of days in the month but I'm trying if possible to keep it constant).
I've seen some information which uses the PMT formula for calculating monthly payments but this takes the interest rate and does a more simply division by 12  unfortunately when I use this function and calculate interest using the A/360 days the balance at the end of the loan term doesn't equal zero.
Can you please help!!
Thanks
Brad
How do i calculate monthly loan repayments that incorporate a monthly account keeping fee? I have used the PMT function before but it doesnt suit this application (from i can figure out anyway). I am a bit of a novice user.
Using the following values;
Initial loan amount: $22,757.50
Interest Rate: 7.95%
Length of Loan: 7 years
and a Monthly account keeping fee: $7.50
;i wanted to replicate the formula used to acheive the following results as set out on my contract;
Monthly replayment: $362.11
Total Amount Paid at end of loan: $30,417.24
and total interest paid at end of loan: $7029.74
Any help would be greatly appreciated.
Hi guys, I posted this a while ago and have asked everyone I know and I still can't work it out!! Is it even possible?
I am trying to work out the interest rate needed on a bank account in order to pay off a loan in the future.
I want to know the minimum interest rate to pay off the loan at a date which is variable without going into the overdraft when the loan is paid.
The attached spreadsheet is a simplified document.
Many thanks in advance,
Alex
Does Excel contain a simple interest loan calculation function built in? Or do I need to specify how exactly simple interest calculates on a per diem basis? What I am trying to do is calculate finance charge based on daily interest such as a standard auto loan contract. I am supplying the interest rate, term, and days to first payment  which in itself contains all of the necessary data for calculation (assuming excel has the loan calculation built in), however simple interest is based daily, so the calculation needs to understand days in each month, and totals days across a long period of time, and I seem to remember excel having basic loan calculation features built in, however general excel help only seems to have basic functions. What would be the best way to approach this?
I have a mortgage loan $125,000 and I pay $300 off loan every two weeks, the fixed rate is 8.50%. I am locked in at this rate for next 24mths (2yrs).
The current variable rate is 6.25% and to break from the existing fixed rate contract will cost me $3,200.
Is there a formula in Excel that I can use to compare the two rates and loan amounts using the 2yr period?
i.e. $125,000 @ 8.50% for the next 2yrs with repayments of $300 every two weeks compared to
$128,200 @ 6.25% for the next 2 yrs with repayments of $300 every two weeks. Is it worth changing over?
Many thanks
Carl
Hello all!
I have seen many online calculators that do this, but I would really like to find an Excel formula, so I can do it on my own computer (and finally understand how its calculated)...
1. You have savings of $10,000.
2. Your bank pays an annual interest rate of 10%.
3. Interest is CALCULATED daily, but PAID monthly at the end of each month.
... and here's the "different" bit:
4. Each fortnight you deposit another $100 into the account.
As mentioned above, I've seen many online compound interest calculators that do steps 1 to 3. There's also many that do step 4. But I really would love to do this on my own computer, so I understand the forumla.
Thanks for reading, and all help much appreciated.
Justine
I have two interest bearing bank accounts and a loan account.
Both of the bank accounts yield a higher interest rate than the interest due on the loan account.
As the account balances grow and the loan balance diminishes, I want to determine where the amounts meet to leave a balance of $10000 in the 7.5% account, $1000 in the 5.1% account and payoff the loan in full.
Unfortunately I keep getting a circular reference error because the balances include the withdrawals and it won't let me analyze the error. (I have Office 97)
Bnk1 Int Rate: 5.1% Beg Bal $10000.00 Monthly Income: $190.44
Bnk2 Int Rate: 7.5% Beg Bal $49073.80
Loan Int Rate: 4.125% Cur Bal $107644.78 Monthly P&I Pmt: $1374.84
When I allow iterate, and maximum iterations 1, it changes all of the cells.
I am very confused as to how to correct this problem.
I'm totally unsure of what iterations does.
Can anyone help?
I have two spreadsheets, and the following columns in Each.
Spreadsheet One: (Loan ammortization)
Pmt Date  Beg Bal  P&I Amt  ExtrPmt  TotPmt  Princ  Int  End Bal 
Spreadsheet Two: Bank Account(s)
MM/YY  7.5%AcctBal  Withdr  Int  5.1%Bal  MoIncDEP  Int  Withdr 
I am attaching a zipped copy of the workbook, as well.
Hi, all! And Happy New Year!! Once again, I'm returning to the pros to see if you can help me......
Here's my problem:
1) I have a loan amount of $86,840.96 that I'm working with.
2) I need to figure out how much interest is due on that loan for a certain period (from 7/14/06  5/14/08).
3) The interest rate is 7%.
4) There have been no payments made on the loan.
5) I just need to know how much interest to add to the loan  based on the foregoing information.
It seems to be an ISPMT formula (is that right?).........but I can't seem to figure out how to identify the parameters. I keep coming up with ridiculous numbers in the hundreds of thousands of dollars....?!
Appreciate any assistance!
Kelly
I am trying to compound principal and interest at a variable percent a day to reach a maximum return of 100%. The interest stays in the account and the principal is subtracted once the interest reaches 100%.
New money can be added at any date. Once the new money reaches 100% return I would like it to be subtracted from the principal.
Here is an example:
Day 1 start with $500. Interest rate is 1% a day. In 100 days it will reach 100% interest. On Day 7 $300 is added and in 100 days it will reach 100% interest. So on Day 7 the Principal balance increases by $300.
ColumnA Row1 starts with a balance of $500.00 and multiplies the interest from ColumnB Row1 (1.00%) and the result in ColumnA Row2 is $505.00. Now the $5.00 in interest becomes Principal on day 2 until it reaches a 100% return.
As you see, the daily interest is compounded as well and when the interest amount earns 100% from the day it was applied then I would like to subtract it from the accumulated principal balance in 100 days.
Now here is the catch, the interest rate can change daily. Day 1 = 1%, Day 2 = 1.5%, Day 3 = 1.75%, Day 4 = 0.50%.
What I would like to know how to program is as the interest rate is added daily is there any amount in Principal or compounded interest that has reached 100% and then subtract it from the running Principal balance.
And if at all possible keep a running total of interest earned until it reaches a minimum reinvestment incriment of $10.00.
I would be willing to pay a reasonable amount for someone to help me code this.
Here is some code
(C,21) =C20+E20+M20 (Accumulated Principal and Interest)
(C,22) =C21+E21+M21
(E,21) =C21*D21 (Accum Principal and Int * Int Rate)
(E,22) =C22*D22
(M20) = (Entry of Principal amount, Example $300)
Thank you,
Stevej
Hi everyone,
I need some help creating a spread sheet for a car loan. I need to have the principal and interest payment for each month of the loan. At the top of the sheet, I need to allow a person to enter in the amount of the loan and an interest rate. The entire table should automatically update when these are changed. The 4 interest rates to compare are based on the interest rate entered. The first one will be the interest rate entered, the remaining will be .25% above the previous rate.
Can anyone help me figure out how I can do this???
Thanks to everyone and anyone who can help!
I could not find an answer exactly on what I need so hopefully this makes sense and someone can decipher it. I am wanting to make a spreadsheet for inputting credit card balances and interest rates and then figuring the compounded daily interest. I have the sheet formatted to how I want it to look but cant seem to make a formula do what I need it to. I wish I knew exactly how to post what it looks like in here. But I will post the basics.
Day Int. Rate Comp. Daily % Balance Payment
1
2
3
4
5
6
Under day, I will have what day in the billing cycle it is. The interest rate should stay the same. From there I would like to have the compounded daily interest rate for each day under that column. The balance column will have the accruing balance based on the compound daily interest and any payments made. Payments obviously go under the payment column. So, basically what I need is a formula to keep a running total for me under the balance column based on the compound daily interest and any payments made during the given cycle. I hope that makes sense. Thank you for all your help on this! I really appreciate it!

