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!

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!

- Bond Accrued Interest (NOT Price) Calculation
- Calculating late fees and accruing interest
- Daily Interest Calculator Nightmare!
- Bifurcate the Accrued Interest portion Financial YearWise 1st Apr XX To 31st Mar XX+1
- XL: Examples of Interest Calculations Outside the United States
- Bank Interest Calculator
- Cell lock, interest calculator formula
- Quarterly Interest Calculating Formula
- Interest calculated daily and charged quarterly
- Getting MIN to work inside of an Array, or other solution
- Calculating compound interest on loans
- Calculating Interest where rate changes per quarter
- Function help with Calculating Interest for two different investme
- Interest rate calculator
- Compound interest calculation
- Interest calculation
- Formulas: Balloon Loan Interest Calculation
- Formula: Calculate Reverse Compound Interest
- Calculate Compound Interest
- Interest Calculation Formula Suggestions Please
- Using Excel to track employee savings/interest calculations
- Spreadsheet for accruing interest and penalties that vary
- Interest Accrual Question
- Loan Compound Interest Function

============================

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

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!

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

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

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

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 FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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.

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

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.

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

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

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.

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

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

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

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

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.

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

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

penalties, where the interest rates change per quarter.

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

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.

No luck finding an answer? You could always try Google.