Finance question - Interest calc on inconsistent cash flow

[cornbread_r_square]

Hello - you may find this is as much a finance approach question as it a tactical excel question. if you think it's better posted in a different forum, plz advise (with the name of said forum!). Tnx.

I’m trying to determine the value of interest that can be collected on the following transaction - in march 2008 a lump sum is lent out; $30,000. The party to whom the loan was made begins repaying with the following inconsistent cash flow:

• $10,000 in Dec, 2011
• $300 in June, 2013
• $750 in July, 2013
• $750 in Oct, 2013
• $750 per month Nov 2013 through March 2015, with a couple months each with double repayment ($1,500)
• $200 in April, 2015
• $750 in Jan, 2016
• $500 in Feb, 2016
• $750 in June, 2016
• $1,000 in Nov, 2016 This was the final payment, making whole the $30,000 in principal.

If interest accrual ends at that last payment, and want to apply the same rate, e.g., i = 10%, how would I determine the interest amount due? Even a rough result would be fine, it doesn't have to be exact down to the dollar.

Thx again

[Richard Buttrey]

Hi,

Are you looking for a single formula?

The easiest way would be to create a table of dates and cashflows and calculate each period.

[MrShorty]

I am not in financials, so I don't know all of the details and nuances of computing interest rates. My first thought is to use the XIRR() function: https://support.office.com/en-us/art...b-a303ad9adc9d

[tim201110]

if priority is to repay nterest then $25,617

[Richard Buttrey]

I noted that the sum of the payments doesn't total 30000 being 750 short.

In the attached I've used the 29250 which gives a total interest paid of £7586 (25.9% of the original amount), which of course is the final balance to be paid.

The XIRR function (i.e. Internal Rate of Return) mentioned by Mr Shorty is typically used by an organisation to calculate a firm's rate of return (interest rate) produced on an initial Capital Investment from a series of varying cash flows from the investment over varying periods.

Purely out of interest I've added that but since the cashflows are exactly equal to the original investment (29250) the answer is of course zero.

However if you regard the original investment as being the original capital sum of 29250 PLUS the interest that has been foregone and yet to be paid by the final balance, i.e. an "investment" of 36836, this generates an internal interest rate of 10.67%

Comparing that with the simple average over the 5 years of 5.3% is I guess explained by the fact that the first month payment of 10000 is significantly large enough to skew the average.

[cornbread_r_square]

That table is very helpful. Thx.

BTW - what does your tag line at the bottom of your post mean, Ctrl+c..., etc.?

[Richard Buttrey]

That table is very helpful. Thx.

BTW - what does your tag line at the bottom of your post mean, Ctrl+c..., etc.?

Hi,

I presume you mean the line beginning 'If any of the responses...blah blah'

Like many learning institutions, particularly those for primary and junior school children, stickers, badges and gold stars seem to play an important part in the rewards regime. This forum is no different!

You are able to click on the 'Add Reputation' item at the bottom left of any post and award a point for that person should you be so inclined if the help has been particularly useful or helpful. As someone accumulates points then the little green squares underneath your name increase in number.

[cornbread_r_square]

Hi - thx very much for your solution, a couple questions. first a big overall question - I can't tell if your worksheet takes into consideration calculation of interest on the interest, e.g., on 12/2011 when $10k in principal is paid towards the original 30,000 loan it's accumulated interest of $11,500. Does that 11,500 amount have interest calculated on it through Nov 2016? That is, OK, you owed me $30k at t=0, then when approx 3.75 yrs later Nov/Dec 2011 rolls around you returned $10k of it. Now you owe me $20k of principal (PLUS that 11,500 of interest, but we're not paying back interest yet, just working on a principal-centric schedule). NOW, by the time you've returned ALL of the principal on 11/2016, you still haven't paid the $11,500 of interest owed as of Nov/Dec 2011. Therefore (for that period) pay me $11,500 of intesest, plus interest from Nov/Dec 2011 through Nov 2016 (?).

A couple finer point questions...

column F - why are values always $250?
Why does the sum of column F (value in F2) have the value of J2 subtracted from it in the in cell J1?
I can't follow what the column values of H, I, and J represent, can you plz explain
This speaks maybe to the above question, but why do values begin changing from 0 at H80, but not before?
Also, basic excel question maybe, but in row 3 why do you inbed the sorts a0, a1, a2, etc in columns A through H, not I then again J through L?

Thx again

[tim201110]

if loan is a priority then $17037
$250 is interest amount in a month

[ZipfelNm1]

I am not quite sure about it.

[Richard Buttrey]

I am not quite sure about it.

Are you ignoring the answer I gave you in post #5 or do you want further explanations?