
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
I am using ACCRINT(30/12/2007, 30/06/2008, 01/05/2008,0.0875,100,2,1) to compute accrued interest on a bond and EXCEL gives me 2.9564.
However, using simple mathematics, the accrued interest is 2.9167(8.75*122/366).
Any help please.
Thanks in advance.
Similar Excel Video Tutorials
Helpful Excel Macros
Delete Empty Columns
 This macro will delete columns which are completely empty. This means that if there is no data within the entire column
Similar Topics
I am stymied by the fact that this function does not decrease the accrued interest when a coupon payment date is passed. For instance, a bond with a 5% coupon rate that pays interest semiannually, issued on 11152006 with a first coupon date of 5/15/2007 should have accrued of $2.5 (per hundred face amount) on 5/15/2007 and accrued of $.0138 on 5/16/2007. The accrint function instead calculates the accrued on 5/16/2007 as $2.5138 in essence failing to account for the fact it just passed the 5/15/07 coupon payment date. What am I doing wrong?
I have four columns with data going down:
A = Date
B = Interest
C = Accrued Interest (Cumulative amount of column B)
D = Interest Paid (negative column C)
At the end of each month interest is paid. So Column D is IF(EOMONTH(A2,0)=A2,C2,0) ; if it's not the end of the month, no interest is paid.
However, on the first day of the month, accrued should reset. So, for instance, accrued interest on 5/3/09 should be the sum of interest on 5/1/09  5/3/09; accrued interest on 6/16/09 should only be the sum of interest on 6/1/09  6/16/09. What formula can I use in column C to sum only the amounts in the current month? I'm trying to avoid doing this manually, as I'm going out several years.
Thank you in advance.
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(TargetData)),
ABS(TargetData),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.
does anyone know how to calculate the interest so it matches this?
i would post the xls file but don't see how.
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 am trying to develop a spreadsheet that will enable me to calculate the exact amount of accrued interest (compounded annually based on actual investment date). Typically I would just use a FV formula, but that only works if there is an investment made DAY 1 and no subsequent transactions afterwards. I am struggling with scenarios in which more than one investment is made over a period of time (at various intervals) or in which a distribution has been made, thereby reducing the amount of accrued interest.
Below is the beginnings of a basic example.
Date Investment Distribution
11/29/07 $100,000 $0
1/28/08 $20,000 $0
10/12/08 $0 ($15,000)
I would like to be able to create either a simple table or a couple of formulas that could easily tell me the amount of accrued interest on any given day. In the past I've done this by setting up a simple ledger that shows ins and outs and interest accruing, but for long investments this can get quite long.
Any ideas?
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/enus/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:
=A4A2
E :
366
N:
1
A:
=A2(A4A18)
Excell price:
=PRICE(A2;A4;A6;A8;A10;A12;A14)/100
Formula:
=(A28+A30A32)/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
=A24A26
I'm a bit confused what the second parameter in ACCRINT does. Microsoft has
documented the calculation of ACCRINT at
http://office.microsoft.com/enus/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/enus
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(TargetData)),
ABS(TargetData),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+DF. 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 23 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 17, 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.CalculatorUpdate.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, $514999K earn .6%, 1534999 earn 1.5%, $3559999 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*(G1F12),IF(B19>=15000,B19*F11/365*(G1F12),IF(B19>=35000,B19*G11/365*(G1F12))))
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: 11032008
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
28AUG2009
9
Date Settled
Activity Category
CUSIP
Description
10
31Aug09
Accrued Interest
","31371LTT6
PAID ACCRUED INTEREST ON THE PURCHASE OF FNMA POOL #255362A 6.000% 8/01/24 SETTLEMENT DATE: 31AUG2009 Asset ","31371LTT6
11
31Aug09
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 28AUG2009 Asset ","912828KF6
12
27Aug09
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 27AUG2009 Asset ","172967BP5
13
26Aug09
Accrued Interest
","912810EK0
PAID ACCRUED INTEREST ON THE PURCHASE OF U S TREAS BDS 8.125% 8/15/21 SETTLEMENT DATE: 26AUG2009 Asset ","912810EK0
14
25Aug09
Income
","31392FPN1
CASH RECEIPT OF INTEREST FNMA REMIC 0274PD 5.000% 11/25/15 DUE 25AUG2009 $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)+1COLUMN($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)+1COLUMN($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

