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:
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?
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?