# Finding relevant financial function

#### konew1

##### Well-known Member
I expect there is a standard function to solve this, but the terminology used in excel help confuses me.

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
AB
1\$ 25.00note face value
240number of notes
3\$ 1,000.00Total investment
536term in months
60.63%default risk
7
8Interest paid monthly
9Assume defaults occur on day 1
10Assume all notes are to different entities
11Round up defaults to integer of notes (example .63% of 40 = 0.252, so round up to 1)
Sheet4

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
It depends a little on how you interpret the interest rate ...

Let's assume each note pays \$25.00 x 15.8% / 12 interest each month = \$0.33 (rounded), so your interest cashflows at time 1,2,3 ... 36 months will be 39 (non-defaulting) x \$0.33 = \$12.87.

You also expect to get back 39 x \$25.00 = \$975 at time 36 months.

So monthly rate of return is
=IRR(B2:B38)
= 1.23% monthly
= 14.8% p.a. nominal (1.23% x 12)
= 15.8% p.a. effective (1.0123^12-1)

Excel 2010
AB
1TimeCashflow
20-\$1,000.00
3112.87
4212.87
5312.87
6412.87
7512.87
8612.87
9712.87
10812.87
11912.87
121012.87
131112.87
141212.87
151312.87
161412.87
171512.87
181612.87
191712.87
201812.87
211912.87
222012.87
232112.87
242212.87
252312.87
262412.87
272512.87
282612.87
292712.87
302812.87
312912.87
323012.87
333112.87
343212.87
353312.87
363412.87
373512.87
3836987.87

<tbody>
</tbody>
Sheet1

Thanks. I didn't give enough detail.
The monthly payments are fixed amount P&I so each month the payment for each \$25 note is \$0.88 with interest in month 1 of \$0.33 and repayment of \$0.55.
Each month the payments stay at \$0.88 but the interest amount od marginally less and the loan repayment is marginally more, to total \$0.88.
At the end of month 36 the payment is the final \$0.88. I can calculate the monthly payment with =PMT(interest/12,no_of_months,Initial_amount)

I have several different interest rate and risk combinations and need to compare them, so was looking for a single cell formula if possible to avoid making a lot of tables running through the 36 monthly transactions

The IRR calculation is based on total repayments. You don't need to split into interest and capital components. For example, here are four different ways you might repay a loan at 10%. All have IRR=10%

Excel 2010
ABCDE
1TimeCashflow1Cashflow2Cashflow3Cashflow4
2Int onlyAccum IntArbitraryP&I
30-100.00-100.00-100.00-100.00
4110.000.0025.0040.21
5210.000.000.0040.21
63110.00133.10102.8540.21
7
8IRR(B3:B6)IRR(C3:C6)IRR(D3:D6)IRR(E3:E6)
910%10%10%10%

</tbody>
1

For P&I repayments of \$0.88 per month per note, cash flow in B2:B38 will be -1000, 34.32, 34.32 ...... 34.32

Monthly rate of return is
=IRR(B2:B38)
= 1.19% monthly
= 14.3% p.a. nominal (1.19% x 12)
= 15.3% p.a. effective (1.0119^12-1)

Thanks, great help

No problems! I'm glad it was useful.

Replies
1
Views
382
Replies
3
Views
1K
Replies
0
Views
397
Replies
2
Views
1K
Replies
7
Views
2K

1,203,250
Messages
6,054,383
Members
444,721
Latest member
BAFRA77

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