Compound Interest And Repayments On Personal Loan 


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!!!
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 Tutorials
PMT Function  Get the Payment Due for a Loan in Excel
How to calculate the payment amount for a loan or similar financial instrument that has a series of constant paymen ...
How to calculate the payment amount for a loan or similar financial instrument that has a series of constant paymen ...
NPER Function  Calculate Number of Periods Needed for a Set of Payments in Excel
How to calculate the number of periods required for an investment in order to get the desired return. The number o ...
How to calculate the number of periods required for an investment in order to get the desired return. The number o ...
Make Your Macros Available in All Workbooks in Excel
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...
Remove Personal Information from Excel Files
How to remove all personally identifiable information from an Excel file. When a file is created in Excel, it auto ...
How to remove all personally identifiable information from an Excel file. When a file is created in Excel, it auto ...
Helpful Excel Macros
Calculate the Future Value (FV) of Compound Interest in Excel  UDF Macro
 This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
 This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Delete All Chart Sheets in Excel  Only Chart Sheets are Deleted  Not Embedded Charts
 Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
 Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Return the ISO Week Number from a Date in Excel  UDF
 Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
 Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
PopUp Message Box When a Range of Cells Reaches a Certain Average
 This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
 This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
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.
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
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 create this spreadsheet as a loan schedule using average daily balance method. (1/payment is constant, fortnightly 2/interest is 5.5% per annum)
In the interest column, at the beginning of each month ( when the day is 1) the interest will be added up from calculation of previous month daily balance.
My idea is that at interest column(let start at 1/08/2013) if (day(A49)=1, average the 30 or 31 cells above E49, 0).
I will manually make adjustment for February where 28 or 29 days applicable.
Can anyone help me with this?
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.....
(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!
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:
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
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
I am after a formula that charges interest on a balance only when a weekly repayment ends before the month end. The interest charge is 3% of the reducing balance.
So if weekly repayments are made only add the interest to the outstanding balance at the end of the month. The attached spreadsheet lists the repayment dates but i need it to detect the last payment in the month and apply the interest. I can then select any start date to see the repayments and interest charged
Thanks
Andy
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.
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
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
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 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 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.
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.
I need to come up with a formula that will tell me what interest rate the loan can increase to in order to pay the loan 1:1.
A1 = $500,000 (Loan Amount)
A2 = 5.50% (Current Interest Rate)
A3 = 30 (Loan Years)
A4 = $2,838.95 (Monthly Payment)
A5 = $50,000 (Income generated to repay the loan)
A7 is where I need a formula that will tell me how high the rate can go and still have the loan repaid on a 1:1 basis.
Thanks,
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
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.
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.
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
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
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.
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.
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 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
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
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
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
Hello Everyone!
I have the below sample loan payment schedule in an excel doc. It automatically calculates monthly payment, interest, principal and loan balance.
Screen Shot 20130619 at 11.38.04 PM.jpg
I'd like to calculate average interest rate in a way that allows me to manipulate the monthly payment. So, for example, the borrower would be able to pay back more in the beginning months, and less in the end months, but still pay back the same amount cumulatively. Does anyone know any formulas I can use or have any ideas?
Thanks!
I have been loaning my brother money over the past 14 months. The loans have been in the form or $1000 per month plus random payments for oneoff expenses like doctors fees. He's not paid anything back yet but we want to know what the total owed is for interest of 10% per annum.
I can easily create a table with payments I've made and the dates with a running total of how much I've paid but how to I create a running balance of what he owes over time based on adding in interest. Honestly I don't care if it is simple or compound or any kind of interest, whatever is easiest. This might end with a oneoff payment in a couple of months, I'd like to calculate what is owed there as a minimum.
Formulas would be nice, a sample spreadsheet would be even nicer.
I thank you and my brother thanks you.