Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

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

Thanks!!!

View Answers     

Similar Excel Video Tutorials

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?

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

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

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

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

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


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


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.

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