I need to calculate effective interest rate for a pool of investments where there is an expectation that some of the investments will fail and provide zero return, with loss of investment capital.

Excel Workbook | ||||
---|---|---|---|---|

A | B | |||

1 | $ 25.00 | note face value | ||

2 | 40 | number of notes | ||

3 | $ 1,000.00 | Total investment | ||

4 | 15.80% | Interest pa grade B4 | ||

5 | 36 | term in months | ||

6 | 0.63% | default risk | ||

7 | ||||

8 | Interest paid monthly | |||

9 | Assume defaults occur on day 1 | |||

10 | Assume all notes are to different entities | |||

11 | Round up defaults to integer of notes (example .63% of 40 = 0.252, so round up to 1) | |||

Sheet4 |