Finding relevant financial function

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,288
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
415.80%Interest pa grade B4
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
 
Upvote 0
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
 
Upvote 0
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%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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)
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top