Interest Calculation

[runeyjam]

I am trying to build a formula that will calculate the correct interest expense as bond instruments are replaced. I have put together the following spreadsheet:

Interest Calculation.xls
	A	B	C	D	E	F	G	H	I	J	K
34						0.5	1	1	1	1	1
35						1/1/2007	1/1/2007	1/1/2008	1/1/2009	1/1/2010	1/1/2011
36						6/30/2007	12/31/2007	12/31/2008	12/31/2009	12/31/2010	12/31/2011
37	Maturity	Interest Rate	Amount	Replaced	Replacement Date
38	9/15/2011	2.000%	840.29	N	1/1/2010	8.4	16.8	16.8	16.8	16.8	11.9
39	6/1/2008	5.875%	200.00	N	3/31/2008	5.9	11.8	4.9	0.0	0.0	0.0
40	6/1/2009	6.850%	200.00	Y	9/30/2008	6.9	13.7	10.3	0.0	0.0	0.0
41	9/1/2010	7.250%	200.00	Y	6/30/2009	7.3	14.5	14.5	7.3	0.0	0.0
42	10/30/2007	5.000%	225.00	N	1/1/2010	5.6	9.3	0.0	0.0	0.0	0.0
Sheet1

The formula seems to work in all cases expect a bond maturing in 2H 2007 that will not be replaced. Here is the formula in cell G45:

=IF(AND($D42="N",YEAR($A42)=YEAR(G$35),$A42>=G$36),$B42*$C42*G$34,IF(AND($D42="N",YEAR($A42)=YEAR(G$35),$A42<G$36),$B42*$C42*YEARFRAC(G$35,$A42),IF(AND($D42="N",YEAR($A42)>YEAR(G$35)),$B42*$C42*G$34,IF(AND($D42="N",YEAR($A42)<YEAR(G$35)),0,IF(AND($E42>G$36,YEAR($E42)>YEAR(G$36),$E42<=$A42),$B42*$C42*G$34,IF(AND($E42<=G$36,YEAR($E42)=YEAR(G$36),$E42<=$A42),$B42*$C42*(1-ROUND(YEARFRAC($E42,G$36),2)),IF(AND($D42="Y",$E42>$A42),"ERROR",0)))))))

Please look at cell G45 which should be around 3.7. I tried adding another “if statement”, but I think the formula is getting too long b/c I keep getting an error message when I try to add the following additional “if statement”:

IF(AND($D42="N",YEAR($A42)=YEAR(G$35),$A42<G$36,YEAR($F36)=YEAR(G$36)),$B42*$C42*YEARFRAC(G$35,$A42)-F42. Furthermore, I think the formula is getting too long in general. Do you have any suggestions?

 

[mortgageman]

What might be helpful (at least for me)

1)Forget your formula for a moment. What is the math. In other words why do you expect 3.7 in cell g45? (And do you mean g42 btw? That is where your spreadsheet is yellowed). What is the calculation that results in 3.7 and why.

2) Why are there replacement dates when replaced = N?

 

[runeyjam]

1) I expect 3.7 b/c they bond expires in 10/30/2007 and I have already accured 1/2 year's interest in F42. So that leaves about 4/12ths (.333) of interest for the year. Therefore, .333*5%*225=3.7. Yes, I mean G42.

2) I do not care if there is or is not a date present in column E if column D=N.

 

[mortgageman]

1) I expect 3.7 b/c they bond expires in 10/30/2007 and I have already accured 1/2 year's interest in F42. So that leaves about 4/12ths (.333) of interest for the year. Therefore, .333*5%*225=3.7. Yes, I mean G42.

2) I do not care if there is or is not a date present in column E if column D=N.

Then why does your formula check if there is a Y in col D and why does your formula refer to col E?

Be that it may - if I were to do this, I would dispense with the long If and replace it with a vlookup. I will try to do this by tommorow - or maybe someone a lot better than me will do it before then.

 

[mortgageman]

Just thinking out loud here - but assuming your interest expense includes the interest associated with the replacement bond (which of course means that you do care if there is a Y or N in column D) and assuming that (based on what you have presented) the rate stays the same - a very strange assumption BTW - I would have the expense equal a sum of two amounts: old bond and new bond. To me this would make the date calculation easier.

 

[runeyjam]

Thanks for your help. I think I got it.

Interest Calculation.xls
	A	B	C	D	E	F	G	H	I	J	K
34						0.5	0.5	1	1	1	1
35						1/1/2007	1/1/2007	1/1/2008	1/1/2009	1/1/2010	1/1/2011
36						6/30/2007	12/31/2007	12/31/2008	12/31/2009	12/31/2010	12/31/2011
37	Maturity	Interest Rate	Amount	Replaced	Replacement Date
38	9/15/2011	2.000%	840.29	N	1/1/2010	8.4	8.4	16.8	16.8	16.8	11.9
39	6/1/2008	5.875%	200.00	N	3/31/2008	5.9	5.9	4.9	0.0	0.0	0.0
40	6/1/2009	6.850%	200.00	Y	9/30/2008	6.9	6.9	10.3	0.0	0.0	0.0
41	9/1/2010	7.250%	200.00	Y	6/30/2009	7.3	7.3	14.5	7.3	0.0	0.0
42	10/30/2007	5.000%	225.00	N	1/1/2010	5.6	3.7	0.0	0.0	0.0	0.0
Sheet1

Here is the formula in G38:

=IF(AND($D38="N",YEAR($A38)=YEAR(G$35),$A38>=G$36),$B38*$C38*G$34,IF(AND($D38="N",YEAR($A38)=YEAR(G$35),$A38<G$36),$B38*$C38*YEARFRAC($F$35,$A38)-SUM($F38:F38),IF(AND($D38="N",YEAR($A38)>YEAR(G$35)),$B38*$C38*G$34,IF(AND($D38="N",YEAR($A38)<YEAR(G$35)),0,IF(AND($E38>G$36,YEAR($E38)>YEAR(G$36),$E38<=$A38),$B38*$C38*G$34,IF(AND($E38<=G$36,YEAR($E38)=YEAR(G$36),$E38<=$A38),$B38*$C38*(1-ROUND(YEARFRAC($E38,G$36),2)),IF(AND($D38="Y",$E38>$A38),"ERROR",0)))))))

F38 is the same without the Sum formula.