# Interest Calculation

#### runeyjam

##### Board Regular
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
ABCDEFGHIJK
340.511111
351/1/20071/1/20071/1/20081/1/20091/1/20101/1/2011
366/30/200712/31/200712/31/200812/31/200912/31/201012/31/2011
37MaturityInterest RateAmountReplacedReplacement Date
389/15/20112.000%840.29N1/1/20108.416.816.816.816.811.9
396/1/20085.875%200.00N3/31/20085.911.84.90.00.00.0
406/1/20096.850%200.00Y9/30/20086.913.710.30.00.00.0
419/1/20107.250%200.00Y6/30/20097.314.514.57.30.00.0
4210/30/20075.000%225.00N1/1/20105.69.30.00.00.00.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?

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### mortgageman

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

##### Well-known Member
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

##### Board Regular
Thanks for your help. I think I got it.
Interest Calculation.xls
ABCDEFGHIJK
340.50.51111
351/1/20071/1/20071/1/20081/1/20091/1/20101/1/2011
366/30/200712/31/200712/31/200812/31/200912/31/201012/31/2011
37MaturityInterest RateAmountReplacedReplacement Date
389/15/20112.000%840.29N1/1/20108.48.416.816.816.811.9
396/1/20085.875%200.00N3/31/20085.95.94.90.00.00.0
406/1/20096.850%200.00Y9/30/20086.96.910.30.00.00.0
419/1/20107.250%200.00Y6/30/20097.37.314.57.30.00.0
4210/30/20075.000%225.00N1/1/20105.63.70.00.00.00.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.

Replies
0
Views
249
Replies
5
Views
227
Replies
5
Views
708
Replies
3
Views
404
Replies
2
Views
116

1,191,120
Messages
5,984,762
Members
439,909
Latest member
daigoku

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back