Accrint

does anyone know how to calculate the interest so it matches this?
thanks
ACTUAL PLC at 6.5% interest
Date******Description*********Debit***Credit **Balance
7/3/2008 GC*****************3000*********3000
7/18/2008 PAYMENT - THANK YOU *******467***2533
7/23/2008 CHQ# **************1791********4324
7/23/2008 CHQ#***************1925********6249
7/31/2008 INTEREST ***********20.28 ********6269.28
8/1/2008 PAYMENT - THANK YOU*********467***5802.28
8/12/2008 CHQ# **************288.75********6091.03
8/21/2008 CHQ#***************372.75********6463.78
8/21/2008 CHQ# ***************2731.7 *******9195.46
8/29/2008 INTEREST**************39.14*******9234.6

i tried ACCRINTM(issue,maturity,rate,par,basis) etc etc!!!
but no success so far.
thanks
PS I tried to edit this post so it would be clearer - columns etc. but when posted the columns vanished!
will try ***

I must write a function for bond price in some other language (doesn't matter) so I looked for a formula for bond (PRICE) price in help (also on http://office.microsoft.com/en-us/ex...092191033.aspx).
There is a good explanation of this calculation and first I wanted to check this formula in Excel .

I wrote a simple example for one bond with one year coupon and compared results of Excel function and my formula and noticed, that there is a small difference. Results are the same only when settlement date is on coupon date (ie. 17.04.2007). When it is between coupons (ie. 04.06.2007), the results of my formula is different. I've checked formula for accrued interests (third part of formula) with function ACCRINT and this part is ok, so difference must be in first two parts of formula.

What did I do wrong?

Regards, Dracan

My example was (copy text in blank woorkbook in A1):
Settlement:
04.06.2007
Maturity:
17.04.2008
Rate:
3,25%
Yld:
4,23%
Redemption:
100
Frequency:
1
Basis:
1
DSC:
=A4-A2
E :
366
N:
1
A:
=A2-(A4-A18)
Excell price:
=PRICE(A2;A4;A6;A8;A10;A12;A14)/100
Formula:
=(A28+A30-A32)/100
1. part
=(A10/((1+A8/A12)^(0+A16/A18)))
2. part
=(100*A6/A12)/((1+A8/A12)^(0+A16/A18))
3. part
=(100*A6/A12)*A22/A18
Difference
=A24-A26

I'm a bit confused what the second parameter in ACCRINT does. Microsoft has
documented the calculation of ACCRINT at
http://office.microsoft.com/en-us/as...089791033.aspx but the
second parameter doesn't appear in the formula. Furthermore the calculation
of ACCRINT was changed somewhere between Excel 97 and Excel 2003:
http://support.microsoft.com/kb/904826/en-us

I use Excel 2003. Is the second parameter in ACCRINT now deprecated? It
doesn't seem to affect the calculation most of the time. However when you
enter some special dates you get weird results. Try for example
ACCRINT("1/1/2000", "2/28/2000", "12/31/2000", 0.01, 100, 1, 0) and
ACCRINT("1/1/2000", "2/29/2000", "12/31/2000", 0.01, 100, 1, 0) - they don't
return 1. Is this a known bug or is there something I don't understand about
ACCRINT?

Boris

Scenario:

Monthly Interest accrued and Payable in the Month of March and September of every year. Each interest period has a different interest rate applied.

1. I need to sum all the accrued interest within a given interest period 1month x 6 month.

I used a formula that I found in J Walkenbach Excel 2002 Formulas book, but I am unable to make it return the right value e.g. payment number.

Formula in B42
{=INDIRECT(ADDRESS(ROW(Data)-MATCH(MIN(ABS(Target-Data)),
ABS(Target-Data),0)-1,COLUMN(Data)+ColOffset))}

I am trying to make it work so it give the correct results
Example
Dates between Periods 22/10/2007 and 22/03/2008 should result 2 in Cell C42
Dates between Periods 22/04/2008 and 22/09/2008 should result 3 in Cell C42
and so forth
Can anyone help with this formula?? Where did I go wrong
Sorry the formatting I had in excel did not translate to HTML well.

******** ******************** ************************************************************************> Microsoft Excel - Loan Calcualtor 1 (version 1).xls ___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout B3 D3 E3 B4 D4 E4 B5 D5 E5 B6 D6 E6 B7 D7 E7 B8 D8 E8 B9 D9 E9 B10 D10 E10 B11 D11 E11 C17 =
A B C D E F G 2 PayNo DATA IntPaymDate Qtr Year NAMED RANGES 3 1 32007 22/03/2007 3 2007 4 1 92007 22/09/2007 9 2007 ColOffset 5 2 32008 22/03/2008 3 2008 Data 6 3 92008 22/09/2008 9 2008 Target 7 4 32009 22/03/2009 3 2009 8 5 92009 22/09/2009 9 2009 9 6 32010 22/03/2010 3 2010 10 7 92010 22/09/2010 9 2010 11 8 32011 22/03/2011 3 2011 12 13 Named Ranges 14 Target 22008 Target 15 ColOffset - 1.00 ColOffset 16 17 Result 1 18 should be 2 Sheet2 (2)
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I have an excel document that contains several sheets specific to each account. Columns A (Deposits), C (Interest accrued for the month) & E (disbursements) contain dates while columns B, D, & F contain the amounts for each transaction. Column G contains the running total for each month B+D-F. I need a formula that can dispurse the amount of interest accrued for the month between the different accounts/sheets assuming there is money in that account at the end of the month in which the interest was earned. For example I accrued $1,146.00 of interest for the month of January 2009. I need a formula to go in column D that divides the $1,146.00 among the different spreadsheets based on each account's current balance on 01/31/2009.

Thanks!

I am trying to write a simple formula to calculate the amount of accrued interest on an investment with multiple in/out dates. In the example below, assume a $100k investment earning 7.50% compounded annually. In addition to the initial investment, assume a capital call (additional deposit) was made a couple years later and then a couple years after that, a distribution was made (assume it goes to pay accrued interest first).

The tricky part - the annual compounding takes place based on the initial investment date, so running FV formulas off of the subsequent investments/distributions is not possible.

I am trying to firm up an investment tracking database for my company's real estate investors, and I'd like to get away from having to build massive spreadsheets that track interest on a daily basis.

Ultimately I'm trying to calculate Interest accrued from a specific date. The problem is payments can be interest only which when used should keep the next months interest at the same rate instead of decreasing because the principal hasn't been paid down.

Im having problems when over the course of a number of payments they are principal + Interest and Interest only which requires at times to add up the same interest amount for 2-3 months in a row, then add up a different interest rate, etc. Additionally if the reference date is say on the 15th and the interest date is the 1st then for the last month I need to calculate the interest on a daily basis based on the current interest rate for that month.

The reference cell is H5, the Date ranges are J2 - J12, and the principal fields a K2 - K12.

In the next post I will include the Amortization schedule.

In this example the interest is accrued at the same rate months 1-7, then decreased for months 8 and 9. so the total interest accrued should be eqaual to: $7487.74 (see next post for schedule).

Microsoft Excel - ZZ_Loan.Calculator-Update.1.1.xls ___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout H6 H7 H8 H9 H10 H12 H13 H14 =
F G H I J K L M 1 v 1.0 Payment
Apply Date Principal + Interest Additional Principal Interest Only 2 1/1/2009 833.33 3 2/1/2009 833.33 4 Quarter Close Backward 3/1/2009 833.33 5 Close Date 9/1/2009 4/1/2009 833.33 6 Days of Interest 0 5/1/2009 833.33 7 Beg Principal Balance $100,000.00 6/1/2009 833.33 8 Paydowns $980.41 7/1/2009 1,321.51 9 End Principal Balance $99,019.59 8/1/2009 1,321.51 10 Net Principle Due $3,562.50 9/1/2009 829.27 11 Interest Due & Accrued 10/1/2009 1,321.51 12 Interest Earned (cummulative) $7,350.66 11/1/2009 1,321.51 13 Interest pymts (cummulative) $7,350.66 14 Net Interest Receivable $0.00 TEST
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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 appreciate any help in advanced. I have promised my boss a Excel Spreadsheet calculating accrued interest to date on judgments we were awarded by first thing in the morning. Now at 3:00am I am ready to give up!

Here are the columns for my spreadsheet related to my question for with an example entry:

Columns Entry
Judgment Date: 11-03-2008
Judgment Amount: $1331.81
Interest to Date: = ?

The interest rate is 10% per annum. I would like the spreadsheet to show the interest presently owed when the spreadsheet is opened. Is this possible?

I have a workbook that I set up to calculate the interest on a loan from inception until today and varying interest rates.

I would like to know whether there is a more efficient way to compute compound interest on a daily basis and if so if someone could assist me

See my sample spreadsheet

******** ******************** ************************************************************************> Microsoft Excel - Compounding Interest.xls ___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout D2 E2 F2 D3 E3 F3 D4 E4 F4 D5 E5 F5 D6 E6 F6 D7 E7 F7 D8 E8 F8 D9 E9 F9 D10 E10 F10 D11 E11 F11 D12 E12 F12 D13 E13 F13 D14 E14 F14 D15 E15 F15 D16 E16 F16 D17 E17 F17 D18 E18 F18 D19 E19 F19 =
A B C D E F 1 Capital Date Interest Rate Days Interest Interest Capital & Int 2 45000 24/05/2006 10.50% 15 194.18 45,194.18 3   08/06/2006 11.00% 56 762.73 45,956.91 4   03/08/2006 11.50% 71 1,028.05 46,984.96 5   13/10/2006 12.00% 56 865.04 47,850.00 6   08/12/2006 12.50% 182 2,982.43 50,832.43 7   08/06/2007 13.00% 70 1,267.33 52,099.76 8   17/08/2007 13.50% 56 1,079.11 53,178.86 9   12/10/2007 14.00% 56 1,142.25 54,321.12 10   07/12/2007 14.50% 126 2,719.03 57,040.15 11   11/04/2008 15.00% 63 1,476.79 58,516.94 12   13/06/2008 14.50% 182 4,230.85 62,747.80 13   12/12/2008 15.00% 56 1,444.06 64,191.85 14   06/02/2009 14.00% 47 1,157.21 65,349.07 15   25/03/2009 13.00% 40 931.00 66,280.07 16   04/05/2009 12.00% 24 522.98 66,803.04 17   28/05/2009 11.00% 77 1,550.20 68,353.24 18   13/08/2009 10.00% 224 4,194.83 72,548.07 19   25/03/2010 10.00% 76 1,514.91 74,062.98 Sheet2  
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hi,

I have the following

Interest Calculation Day -23rd of every month (say B1 = 23)

Date Interest Interest Sum
A5 (Onwards) B5 Onwards C5 Onwards
01/01/2008 $232
15/01/2008 $230
23/01/2008 $230 $692
29/01/2008 $230
12/02/2008 $230
23/02/2008 $230 $690
26/02/2008 $230


I want to sum the interest on every 23rd of month in Column C which will be added to the loan amount. Can someone assist me to write a formula for this?

Thanks

I have a file that contains data and a date. However, in Column D the date is not always the same as the date supplied in column A.

When the dates do not match, I want the date in column D to replace the date in column A. If the activity category is accrued interest, we will leave the date alone in Column A.

The function I wrote is the following:

=IF(B11="Accrued Interest",A11,IF(A11=MID(D11,FIND("DUE",D11)+4,11),A11,MID(D11,FIND("DUE",D11)+4,11)))

I would like to however; create a VBA script that loops this formula for all rows of data and the replacement date is kept in Column A (thus overriding the original data.

Aug DDKY Detail3


A B C D 8 28-AUG-2009 9 Date Settled Activity Category CUSIP Description 10 31-Aug-09 Accrued Interest ","31371LTT6 PAID ACCRUED INTEREST ON THE PURCHASE OF FNMA POOL #255362A 6.000% 8/01/24 SETTLEMENT DATE: 31-AUG-2009 Asset ","31371LTT6 11 31-Aug-09 Income ","912828KF6 CASH RECEIPT OF INTEREST U S TREAS NTS 1.875% 2/28/14 $0.00094792/$1 PV ON 150,000.0000 PAR VALUE DUE 28-AUG-2009 Asset ","912828KF6 12 27-Aug-09 Income ","172967BP5 CASH RECEIPT OF INTEREST CITIGROUP INC NT 5.625% 8/27/12 $0.028125/$1 PV ON 75,000.0000 PAR VALUE DUE 27-AUG-2009 Asset ","172967BP5 13 26-Aug-09 Accrued Interest ","912810EK0 PAID ACCRUED INTEREST ON THE PURCHASE OF U S TREAS BDS 8.125% 8/15/21 SETTLEMENT DATE: 26-AUG-2009 Asset ","912810EK0 14 25-Aug-09 Income ","31392FPN1 CASH RECEIPT OF INTEREST FNMA REMIC 02-74-PD 5.000% 11/25/15 DUE 25-AUG-2009 $0.00417/PV ON 18,754.06 PV DUE 8/25/09 Asset ","31392FPN1


Spreadsheet Formulas Cell Formula D8 =IF(B11="Accrued Interest",A11,IF (A11=MID (D11,FIND ("DUE",D11) +4,11) ,A11,MID (D11,FIND ("DUE",D11) +4,11) ) )


Cell D8 gives an example. It is looking at Row 11. In this row, the date says Aug 31 however in Column D of the same line, the date is Aug 28. Therefore the result in cell D8 is Aug 28.

I want Cell D8 to be cell A11.

Does anybody know how I can incorporate a loop function with this formula so that I can do this for all of my data with the click of a button. I know this can be done with a function, but I honestly need the date to fall in Column A and for it to be a date and not a formula (therefore in the end I need a paste special -> values).

If anybody can help with this, I would greatly appreciate it.

Thanks!

Hi everyone,

Thanks in advance for your help.

I am trying to write a model to show interest over a period, for a money market fund. The only inputs I want to have are settle date, redemption date, principal investment and interest rate.

Interest is calculated as follows:

Principal * rate * (days/365) = interest

Here is the problem,

Interest is paid on the first of the month, for the days invested the previous month. For instance, on Feb 1, interest is paid for the number of days invested in Jan. The interest is accrued off the reinvested principal + interest for the following month.

Assumptions: Balance never changes, except interest payments (no withdrawls or contributions). Interest rate never changes

I made a sheet, but it is big, and requires a lot of manual data, as the days/month varies each month. How can I make this better?

So I have a spreadhseet that has two columns. Column A holds the date and Column B specifies what it is.

I want to in VBA code skip the line if the line in Column B says "Accrued Interest".

However, if it is anything else, I want to format that date to "dd/mm/yyyy" format.

Is there an easy way to do this. I'm having difficulty on skipping a row based on if the cells in Column B = "Accrued Interest".

Thanks in advance!

I want to calculate interest on an old account. If the principle is $3300, account is 68 months old and interest accrues at 1.5% monthly would this be the correct formula to give me the total due including principle and interest:

$3300(1+0.15)^68

I have a list of entries in a database, with a date at the beginning of each.

Code:

01/08/2008
04/08/2008
05/08/2008
05/08/2008
05/08/2008
05/08/2008
05/08/2008
05/08/2008
06/08/2008
06/08/2008
07/08/2008
07/08/2008
07/08/2008
08/08/2008
08/08/2008
11/08/2008
11/08/2008
12/08/2008
12/08/2008
12/08/2008
12/08/2008
12/08/2008
12/08/2008
12/08/2008
12/08/2008
13/08/2008
13/08/2008
13/08/2008
13/08/2008
13/08/2008
14/08/2008
14/08/2008
14/08/2008
14/08/2008
14/08/2008
15/08/2008
18/08/2008
18/08/2008
18/08/2008
18/08/2008
18/08/2008
19/08/2008
19/08/2008
19/08/2008
19/08/2008
20/08/2008
20/08/2008
20/08/2008
20/08/2008
22/08/2008
22/08/2008
22/08/2008
27/08/2008
28/08/2008
28/08/2008
28/08/2008
01/09/2008
01/09/2008
01/09/2008
01/09/2008
01/09/2008
02/09/2008
02/09/2008
02/09/2008
02/09/2008
02/09/2008
02/09/2008
03/09/2008
03/09/2008
03/09/2008
03/09/2008
04/09/2008
04/09/2008
04/09/2008
04/09/2008
04/09/2008
04/09/2008
05/09/2008
03/09/2008
09/09/2008
09/09/2008
09/09/2008
09/09/2008
10/09/2008
10/09/2008
10/09/2008
10/09/2008
11/09/2008

Like so.

I know how to use the count function so it will say 4 above 10/09/2008 etc.

But how do i get these count totals on another sheet, within the same spreadsheet?

Thank you for your time.

Hello all MrExcel Message Board viewers! I am having a difficult time trying to get a formula to work. The formula ACCRINT() formula doesn't appear to be defined (I was receiving a 'sub or object not defined' error). So I thought that I would have the formula text entered into the cell, but now I cannot properly reference the cells. I think that this is because its been a long week. Anyway, can anyone help me with the correct way to reference the cells within the formula. My code is posted below... (By the way, this code is associated to a command button within a user form.) Thank you, in advance, very much!

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Warrant Data")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for an Issue Date
If Trim(Me.IssueDate.Value) = "" Then
Me.IssueDate.SetFocus
MsgBox "Please enter the Issue Date"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.IssueDate.Value
ws.Cells(iRow, 2).Value = Me.TransitDate.Value
ws.Cells(iRow, 3).Value = Me.SerialNumber.Value
ws.Cells(iRow, 4).Value = Me.Amount.Value
ws.Cells(iRow, 5).Value = Me.MaturityDate.Value
ws.Cells(iRow, 6).Value = 0.0375
ws.Cells(iRow, 7).Value = "=ACCRINT(Cells(iRow, 1), Cells(iRow, 5), _
Cells(iRow, 2), Cells(iRow, 6), Cells(iRow, 4), 1, 2, True)"

'clear the data
Me.IssueDate.Value = ""
Me.TransitDate.Value = ""
Me.SerialNumber.Value = ""
Me.Amount.Value = ""
Me.MaturityDate.Value = ""

End Sub

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

=SUMPRODUCT(($S3:W3)*((1.1)^(COLUMN(W3)+1-COLUMN($S3:W3))))

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

=SUMPRODUCT(($S3:W3)*((1.1)^(MIN(3,COLUMN(W3)+1-COLUMN($S3:W3)))))

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 Min(3,{5,4,3,2,1})=1 where what I'm hoping for is an array of {min(5,3),min(4,3),min(3,3),min(2,3),min(1,3)} to multiply by the array of cashflows.

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 values are calculated as: 1.1^1, 1.1^2, 1.1^3 + 1.1^1, 1.1^3 + 1.1^2, 1.1^3 + 1.1^3,1.1^3 + 1.1^3, 1.1^3 + 1.1^3 + 1.1^1,...

Thanks!

P.S. - I hope my first post is worthy. I've used this board for some time and have generally been able to find an answer without posting. However, my search for 'Min and Sumproduct" was not fruitful.

Hi All,

I am hoping that someone can help me with this one, I've been working on it for a while and it has me totally stumped.


******** language="JavaScript" ************************************************************************> Microsoft Excel - Book1.xls ___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout G2 H2 G3 H3 G4 H4 G5 H5 G6 H6 G7 H7 G8 H8 G9 H9 =
A B C D E F G H 1 Payment Date Interest Rate Interest Start Date End Date 2 1/01/2008 10.00% 1/01/2008 31/03/2008 3 1/02/2008 15.00% 1/04/2008 31/03/2008 4 1/03/2008 20.00% 1/07/2008 30/06/2008 5 1/04/2008 1/10/2008 30/09/2008 6 1/05/2008 1/01/2009 31/12/2008 7 1/06/2008 1/04/2009 31/03/2009 8 1/07/2008 1/07/2009 30/06/2009 9 1/08/2008 1/10/2009 30/09/2009 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Basically I need a formula, located in column B that will check the date in column A against the dates in columns G & H and if its equal to or between those dates return the interest rate that relates to that date range.

Any help on this is massively appreciated

David

i am trying to keep track of the interest i am accruing on my c.d. i am
keeping track of it on excel. the only problem is i need to know how to
set up the calculations in excel. i have 1500 in my c.d and the rate is
3.92% with a 4.0% apy. the interest is accrued daily and credited
monthly. can anyone help