Free Microsoft Excel 2013 Quick Reference

accrued interest calculation

I am attempting to calculate accrued interest on day end balances for the month. $0 to $4999 earn 0% interest, $5-14999K earn .6%, 15-34999 earn 1.5%, $35-59999 earn 1.75% and $60000 plus earn 2.25%.

I would like a set formula that can be used throughout my spreadsheet (for 31 days of the month) that will calculate daily what the interest is on the closing day balance based on the above parameters.

I have started with this =IF(B19>=5000,B19*E11/365*(G1-F12),IF(B19>=15000,B19*F11/365*(G1-F12),IF(B19>=35000,B19*G11/365*(G1-F12))))
However,it is only working for balances between $5000 and 15000 so obviously I am missing something!

Thanks in advance for your assistance!


Dear VBA Gurus,

============================
Date: 13-Sep-05 {using fn =TODAY(), for instance}

Bond Coupon Maturity Par Freq AccrInt
B1 5.00% 15-May-2015 10,000,000.00 S ???
B2 10.00% 31-Oct-2012 10,000,000.00 Q ???
============================

In Excel, using in-built functions as far as possible (eg. ACCRINT, etc.), how do I calculate the Accrued Interest figure (as of today, and w.r.t. the last coupon date) for each of these 2 bonds? The last coupon dates, based on each bond's stipulated coupon frequency (Semiannual for B1, Quarterly for B2), would have been 15-May-2005 and 31-Jul-2005, resp.

I tried using ACCRINT, but couldn't really get logical answers with it. My back-of-the-envelope estimates for the accrued interest, till today (13-Sep-2005), on these 2 bonds are $123,000.00 and $40,000.00 resp.

Please advise what formulae I should be using...

Thanks in advance.

Indebtedly yours,
Om

I would like to set up a worksheet to automatically calculate the total amount of accrued interest and late penalties on a delinquent customer.
Our policies are: 10% penalty on any amount due not paid by the first, plus 10% annual interest on any amount due.
I have several very delinquent clients who we are tracking amounts due in preparation of a coming lien sale. I'd like to have a simple worksheet that I can send to my directors that will automatically update - these individuals are making no payments.
I've attached a sample of what I'm thinking of. I've been tracking using the manual method on the second tab, but I wonder if there is a clean formula I can use to track int more automatically and cleanly, as in the first tab.
I am looking for the function or formulas that will get the right result in the yellow highlighted ares areas in the "A little Automated" worksheet.

Thanks!

Hi all,

I would greatly appreciate some advice/ help.

I have a spreadsheet in Excel that is ultimately used to calculate the monthly payment amount that is required for a mortgage in order for it to be paid off in a certain timeframe.

I am required to input the following:
Mortgage amount
term in months
interest rate
date payment is made

the outputs are based in daily rows and are:
date
month day number
day in week (1-7 for payments not on weekends)
daily accrued interest
cumulative accrued interest
opening balance
interest applied
payments
closing balance

(I can host the spreadsheet if someone wants to see it, but it is 24Mb)

Basically, the issue is the interest must be calculated daily and added monthly.

The other issue, is that interest is added monthly, but only after pre-notifying the interest to be charged (by 21 days).

In addition to this, payments to the mortgage cannot be made on a weekend, so this means slightly more interest is added.

At the moment I can only calculate 20 years worth, this takes me to 7500 rows and massive amounts of calculations.

I actually need it to be able to calculate to 45 years, so I think it will just crash at the rate its going!

The interest can also be tiered, so the fir 3 years can be at one rate, the next 3 at another and a further tier, I have to have 3 sheets, one for each tier!

I really cant see an easier way of doing it, it may sounds simple but its pretty complicated for me!

Any advice would be appreciated,
thanks all,
Alex

Bifurcate the Accrued Interest portion Financial YearWise 1st Apr XX To 31st Mar XX+1

Dear Forum,

I am Filing my Income Tax Returns and require some help in calculating the Notional Accrued Interest portion Financial YearWise .

In India, the Financial Year begins from 1st April XXXX to 31st Mar XXXX+1.
My Chartered Accountant has suggested that any Income arising out of Term Deposit Receipts should be filed in advance though it may be maturing in the next Financial years to come..
This is done by calculating the Notional Interest that can be achieved with the Term Deposit in each Financial Year...separately

I am using the Traditional Formula : = RECEIVED(OpnDt,MatDt,Amt,Int%,Basis)

Not exactly sure whether this would be good in getting the actual maturity value like the bank.
So please feel free to suggest any other formula which can suffice the requirement.

However, my actual need is to be able to Split or Bifurcate the Interest component in different Financial years.

Ex:-
Investing Amount -Rs 10,00,000
Opening Date- 15-Feb-08
Maturity Date- 24-Apr-12
Int %------------- 5.75
Basis------------2

= RECEIVED(OpnDt,MatDt,Amt,Int%,Basis)

Maturity Value- Rs 1,51,515
Principal- Rs 1,00,000
-----------------------------------------------
Interest Component- Rs 51,515

As a One-Time Maturity amount is received after the Term Deposit is matured on Maturity Date- 15-May-08 and this seems easy to calculate using the in-built formula, however , I require to split the Interest component accruing every Financial year wherever applicable.

Ex:
Opening Date- 15-Feb-08
Maturity Date- 24-Apr-12
Tenure : 1 Year, 4 Months and 16 Days.

I need to calculate the below :

Interest portion from 15-Feb-08 to 31-Mar-08 This would change the Principal + Int for the next step

Interest portion from 1-Apr-08 to 31-Mar-09 This would change the Principal + Int for the next step

Interest portion from 1-Apr-09 to 31-Mar-10 This would change the Principal + Int for the next step

Interest portion from 1-Apr-10 to 31-Mar-11 This would change the Principal + Int for the next step

Interest portion from 1-Apr-11 to 31-Mar-12 This would change the Principal + Int for the next step

Interest portion from 1-Apr-12 to 24-Apr-12

The portions marked above in the RED font is the Starting and END and is Uneven and BLUE FONT is from one FInancial Year to the other.

This is more to do with placing the interest component in different Financial Years so the reference of the Opening Date, Maturity Date and the Principal in reference changes..

Please see the attachment

Warm regards
e4excel

XL: Examples of Interest Calculations Outside the United States

Microsoft financial functions presume customary financial practices in the United States. Other countries have different laws about quoting and computing interest. This article ...

I'm an amateur to macro as I'm only in a low level class at a university... But I'm attempting to make a macro for a bank interest calculator. It asks your type of account(which then assigns an interest rate to it), how much money is in the account, and also how long th emoney will be in the account.

I used a "Select Case" for the account types, but I seem to be struggling for it to work, it won't put the value of the total into the assigned cell, or it's just not computing it(as I get "0" each time I run it)...

I am open to any help or suggestions... Here's the code(I took out some of the page modification ones to give you the lines of code that are causing problems)

EDIT: Also curious if I should change the "Select Case" to an "If ... Then" alignment


	VB:
	
 BankCalculator() 
     'Bank Calculator for different accounts
     'declare variable
    Dim shtBank As Workbook, strAct As String, intMon As Integer, strLong As Integer, intTotal As Double 
    Set shtBank = Application.Workbooks("Bank Calculator.xls") 
     'input box for amount of money, assign address
    intMon = InputBox(prompt:="How much money do you currently have in the account?", _ 
    Title:="Amount", Default:="0") 
    Range("d6").Value = intMon 
     'input box for maturation/length of time, assign address
    strLong = InputBox(prompt:="How long will it stay in the account? (In Days)", Title:="Length of Time") 
    Range("d8").Value = strLong 
    Range("d9").Value = Now + strLong 
     'input box for account, assign to cell, also create formulas for different inputs
    strAct = InputBox(prompt:="What kind of account do you have? (Checking, Savings, Money Market)", _ 
    Title:="Account Type") 
    Range("d4").Value = strAct 
    Select Case strAct 
    Case "Checking" 
        [intTotal = (intMon * 1.005) * (strLong / 365)] 
    Case "Savings" 
        [intTotal = (intMon * 1.005) * (strLong / 365)] 
    Case "Money Market" 
        [intTotal = (intMon * 1.005) * (strLong / 365)] 
    Case Else 
        strAct = InputBox(prompt:="What kind of account do you have? (Checking, Savings, Money Market)", _ 
        Title:="Account Type") 
    End Select 
     'assign address for total amount
    Range("d11").Value = intTotal 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I use EXCEL 97.
I will b very thankfull if you solve these problems.

1. Can I lock particular cell If Yes,How?
I want to lock cells which contains formulas.

2. If cell K7 shows 11 I want to see G11 in H9.
Same if Cell K7 shows 201 I want to see G201 in H9.

Its a interest calculation sheet where K7 is number of days amount invested and COLUMN G shows interest accured.

Please help.I'll be very thankfull to you.

Hi ,

I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly.

But I want to make it compact , as d one I created is long enough.

A3 = Principal Amount
B3 = Date of Investment
C3 = Interest as on Date
D3 = Number of Days , amount Invested {comes out of formula set}
E3 = Rate of Interest

Now in F3 I want the Interest amount , compunded quarterly.
Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?

Please let me know the formula , and help me to make my calculator a bit smart.
Regards

Hi guys.

Hope you can help. I dont know where to begin. I can calculate on paper but it takes to long. There must be an easier way.

I have a home loan where the interest in calculated daily on the outstanding balance and charged to the account quarterly (the interest is added to the principal quarterly)

On paper i do the outstanding daily balance * interest rate = x
Then i will calculate x/360 = daily interest. Then i repeat every day for a quarter based on what is outstanding (Ie that will change once i make a payment)
Then i add them all together to work out what the total owing is.

I would like to be able to use and excell formula that will work out the interest that will be charged at the end of that quarter if i make additional payments on x day in that period. (The x day will differ sometime the extra payment will be at the start of the month and sometime it will be at the end and i would like to be able to work out if i make more than one x number of payments)

Ie I generally pay into the loan every week (but the amount is different every week)

In addition...
I know it may be difficult do you think it is possible to work out if i pay x amount into the loan per week (Amount the same) interest calculated daily charged at the end of the quarter, if it is possible to work out the time it would take to pay off the loan?

Thank you in advance.

I have a file that has, in the third row, cashflows on which interest is to be accrued (in this case, compounded at 10% per period). I am trying to show the cumulative value of those cashflows plus accrued interest, calculated in a single row. So, for example, in 7th period the formula I came up with is:

=SUMPRODUCT(($B3:H3)*((1.1)^(COLUMN(H3)+1-COLUMN($B3:H3))))

Now, I need to introduce a dynamic limit by which interest is only compounded for a certain period of time, denoted in cell A4. Trying to limit the interest to three years, I tried:

=SUMPRODUCT(($B3:H3)*((1.1)^(MIN($A4,COLUMN(H3)+1-COLUMN($B3:H3)))))

However, the MIN function appears to be outside of the array as it keeps returning "1." So, I think what is happening is that I'm getting, where A4=3, Min(3,{7,6,5,4,3,2,1})=1 as opposed to the array that I'm hoping for of {min(7,3),min(6,3),min(5,3),min(4,3),min(3,3),min(2,3),min(1,3)} to multiply by the array of cashflows.

I'm not tied in any way to my original formula and would also appreciate wholesale new formulas that accomplish the same task as well. The main point of the formula is to be able to multiple one array of values by another array of time that puts a maximum on the amount of time that has elapsed since the date of that cashflow. Just to be clear, the result that I'm looking for would look something like this:

Investment by Year: 1,0,1,0,0,0,1,... (it goes on for 50 or so columns)
Ending Value by Year: 1.1, 1.21, 2.43, 2.54, 2.66,2.66,3.76,...
Where the calculation for the 7th data point should be 3.76 = sumproduct{1*1.1^min(7,3), 0*1.1^min(6,3), 1*1.1^min(5,3), 0*1.1^min(4,3), 0*1.1^min(3,3), 0*1.1^min(2,3), 1*1.1^min(1,3)}

Thanks!

I am trying to calculate how to repay interest on several loans that were provided by one person over a 25 year period.

Example: Following are 2 loans showing the dates and amounts of each loan I received over the years. There are several others received thereafter at different dates. I would like to show all transactions on1 worksheet. with repayment amount for each loan plus interest (eg 2.5 compounded quarterly), & breakdown of original amount of loan, amount of accrued interest and total repayment amount. .

1. 8-15-1983 loan amount received=$23,000.00

2. 5-25-1986 loan amount received= $30,000.00

TOTAL: $53,000.00
1.How would I go about setting up a spreadsheet to illustrate above items?

2.What would be a fair way to account for the time value of those loans for each period to the deadline date of September 30, 2007?

Many thanks, Ron

A tax liability has been found, for simplicity say it is three years old.
The tax authority announces its Interest rates per quarter, and often change.
To calculate the accrued interest due after three plus years, has does one
go about that? The samples all seem to suggest a constant interest rate. If
only it were that simple.

1. Firstly i want to calculate interest for an Fixed Investment
eg.. 2500 is invested at 9% p.a. rate of interest for 120 months i want to
calculate the accrued interest and Amount of Investment (Principal Amount +
Interest) for end of 12, 24, 36 ....etc. till 120th month.

2. Second i want to calculate interest for recurring deposit say 100 is
invested every month at a rate 9% for total period of 120 month then interest
and principal amounts at end of 12, 24, 36... etc. till 120th month..

I want a formula to calculate the applicable yield for bonds.

There are many formulas available readily but none calculate the yield when the bonds already have some interest accrued.

I am buying a bond which has been issued on 17th Sep 2011 face value 1000 interest rate 12.25%
The maturity date is 17th September 2016
The annual interest of 122.50 shall be paid every year on 17th September

The bond has started accruing interest from the date they have been issued

what should be the formula to calculate the effective yield of the bonds if I buy today at 980

Thanks for advising

I think my bank has been underpaying me interest on a 5 year certificate of
deposit.

How can I calculate the interest I should be getting on, say, $2,500 for a
five year term at 6.77% and show how much interest accrues at year one, year
two, etc.?

Ike

Hi

I would like to calculate interest on a loan. The interest needs to be calculated on a daily basis. The actual interest rate will only change a few times during the year. There are also sundry payments being make to and from this loan. I would like to see a running total of this loan with balances at the end of each month but also be able to see the balance of this loan at any given day.

Any idea?

I'm after the formula to calculate Balloon Loan payments, specifically the interest amount paid each month of a term on say a car loan.

I've used the PV and PMT/IPMT functions to come up with the monthly payment amount but i want to find out how to work out the amount of interest i would be paying each month and the amount of capital:o

I am having trouble finding a formula to calculate the initial ammount required to obtain a savings ammount after a period of time when there is compound interest involved.

For example:
If i wanted a total savings after 10 years of £100,000. How much would i need to pay monthly into my savings account if I get 5% annual interest calculated monthly.

Does anyone have a formula for this please. It would be very helpful as I have spent all day trying different variations on the FV function and others to try and work this out.

Many thanks
Michael.

I am trying to create a calculator based on a worksheet I have. I do not know how to write a formula for simple interest calculations and for compound interest calculations. Any help would be really appreciated.

I am looking for a formula to calculate 6% interest between a Start date and an End date (inclusive of start and end dates)

Not Compund Interest, each year is calculated separately.

The first year currency balance is calculated at 6%
The Second year currency balance is calculated at 6% and added to the First year
The remaining days are calculate at 6% with respect to the year and added to the previous subtotal total to give the total interest amount I am seeking between the Start Date and End date.

A suggested Formula
14000*6%*DATEDIF(A1,A2,"y") + 14000*6%*F/365, where "F" is the number of remaining days.

Question 1 with above Formula
How can I include a calculation for "F" in the formula above?

Question 2 with above Formula
It has been suggested that DATEDIF may not be a good function to use for this, does anyone know why?

Question 3
Can anyone provide a simpler function to perform this calculation?

Question 4
Heres modification to the Formula that has been suggested to me to account for leap years
Instead of 365, write (date(1+year(A1),1,1)-
date(year(A1),1,1)), if you want to use 366 in leap years.

Any suggestions regarding each the 4 questions above would be much appreciate.

Thanks in advance

I really do hope I'm posting this in the correct forum.
My boss has requested that I set up a program to track our company's employee savings program. We're a small company and have probably only about 20-30 people in the savings program. My boss thinks I know more about Excel than I do, although I am willing to learn.
Our company is currently using a program to track the employee savings but the program is quite old and no longer supported.
I've been asked to set up an Excel file to show quite simply the employee's name, the date and amount of contribution, running balance, and also calculate simple daily interest which is compounded monthly. He also wants to be able to print off "statements" each month for each employee in the plan.
I'm not sure Excel is the correct program to use. I can't even begin to think of how the data could be entered easily.
What I'm looking for is any advice/suggestions about whether you think Excel can be used for this and also any other programs or sites out there where I can possibly find more information and/or help in getting this set up and going. Or any existing programs which can perform this function?
Thanks in advance for any help you can provide.
~martih

Trying to create a spreadsheet that calculates compounding interest and
penalties, where the interest rates change per quarter.

I have searched all over the internet for the answer to this question, so
please forgive me if it has been asked before.

I have written a simple spreadsheet to allow me to serve as a bank for my
daughter (6 years old). She gives me her money and I enter it into the
spreadsheet. Using datedif between the date of her deposit and TODAY it
calculates the simple interest that has accrued and totals the amounts.

All of this works great, until she wants to make a withdrawal. I need to be
able to reduce the amout she has on deposit, without affecting the already
accrued interest.

There may be a better way to write this- I tried to use ACCRITN, but I
couldn't figure out how to do it that way.

I would appreciate any pointers anyone has.

Thanks

I'm trying to create a function to allow me to calculate total interest paid
on a loan.

* The interest is calculated daily (using "annual rate/365" to calculate
daily rate)
* Accrued interest is applied to the loan monthly on the last day of the
calender month.
* Payments will be regular, either fortnightly or monthly.

This will be a generic function, but needs to be accurate as it may be
potentially used for large amounts / long periods. I can't work out how to do
this without filling a sheet with daily calculations.