Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Compound Interest And Repayments On Personal Loan

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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


View Answers     

Similar Excel Tutorials

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 formu ...
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 ...
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 ...
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 ...

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
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
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
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
Pop-Up 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

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?



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


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 date--it 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:


this is in B2, and answer is 7%

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!


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



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:


Help is much appreciated.




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.


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.



Annual Interest Rate- 12%
Loan Period in Years- 5
Number of Payments Per Year-12
Start Date-1-12-2009

Payment (per period).................
Number of Payments...................
Total Interest Paid...................
Total Interest.......................
Total Payment........................

Payment Date:-1-1-2010
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. 250000-4167(round figure)=Rs.2,45,833)

Interest: interest calculate first month Rs.250000, second month 245833, third - 241667, 4th, 5th ....60th.

Balance:loan amount-principle 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.


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!!



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.

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


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.

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.


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!


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.

Column-A Row-1 starts with a balance of $500.00 and multiplies the interest from Column-B Row-1 (1.00%) and the result in Column-A Row-2 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,

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 2013-06-19 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?


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 one-off 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 one-off 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.