WOW ... today got my first autolend since 14 December !!!
Printable View
WOW ... today got my first autolend since 14 December !!!
thats pretty much it.
I got a spreadsheet that had expected returns for each of the 6 risk grades, based on all the available stats for each (annual default rates, avg time to default, paid off early rate, avg time to pay off early etc) and modeled returns on each one.
It came out D/E was best return - which we already discussed.
I then ran recession scenarios, ie default rates increase by X % over Y period of time, to find out what would happen to expected returns. I found there is a lot of room to move. On portfolio similar to mine (actually the target I had in mind, it was something like 10% / 20% / 20% / 17.5% / 17.5% / 5%, I would need default rates to increase 5 fold, for my EV = 0.
Granted, thats saying they all just increase by 5 fold. Which when the average default rate of an A is 0.17%, increasing this 5 fold is still not even 1%. So still f all. Realistically in a recessions default rates might be more of an (default rate x 3) + 5% type thing.
Would need some more research on modeling how default rates react to recession scenarios.
Also thought about tracking lead indicators that a recession could be coming, ie unemployment rates etc
Does your spreadsheet take into account tax and fees ? Does it take into account early repayments and the effects of having to invest in lots of $25 in order to take avantage of continuous interest earnings ? You would almost need to write a program todo all that ? Paying tax/fees as you earn it - has a huge effect on your earnings at the end.
I think your calculation at the end for returns over years - is not the same as compounding interest.
I think its incorrect to come in at 85 loans at the start and 85 loans at the end - as you would be over time you would be reinvesting in new loans as payback happens etc. The interest you earn would be put into new loans - new loans risk profiles.
Food for thought.
The tax and harmoney fees are very easy to do.
The only thing that gets complex is reinvesting. So i don't have that in the model. You can't really add re invested loans to existing loans as payment periods are different etc, has to be a new line in excel and then this in turn has its own stuff reinvested the next period. Kind of like compound interest, except the compound is its own loans with variable characteristic (interest / default rate, % early payback etc etc).
Though I have modeled the scenario that all payments coming out of harmoney go into a managed fund at 8%.
For simplicity i assume investing in chunks of $100
To be more specific I use IRR, ie goal seek on a particular portfolio balance what default rate multiplier (ie simulation of a recession) makes my IRR go to zero.
I pretty sure I have everything exact, i can download a csv from harmoney, feed it into my model, and on my summary page it spits out a (pretty much to the nearest cent) a reconciliation with my harmoney dashboard.
Attached calculation. Maybe its wrong though. You may wish to go for your managed fund at 8% as you will get more. Does not take into account you would be reinvesting the returned amounts.
What interest rate have you used?
25% on 8200 as per www.bankrate.com amortization calculator type tool.
%15 harm fee.
17.5 Tax.
Roughly a new loan can be undertaken every 10 days. So you lose some interest on those days while you wait.
I should have used 20% for harmony fees structure which would bring it down to 5.37% after tax.
So a critcial part of your spreadsheet is the calculations of reinvestment amounts as they come in - without it it wont be very good.
Somewhere in the forum is a modeling tool which tries to do it more correctly.
Yeah, nah...
The discussion was about E and F loans so you should have used at least 35%...
You've assumed the defaults are from day one which is not reality - perhaps 1/2 on day one and 1/2 at the start of year 2 - Harmoney state avg default time is 18mths...
What about taking tax of the 8%...got to compare apples with apples...
You've assumed loans end at 2 years, again, not reality. Plug in 35% and even without the other details you'll see that 8% won't come near it...
I was just using your numbers and assumptions from your previous post.
Harmony has a graph which you can use to predict defaults - most defaults acording to them happen near the 3 month point and then tail downward
its at the bottom of this page
https://www.harmoney.co.nz/investors/investment-risks
called the hazard curve down the bottom.
Which maps to the below array values - which you can use in your spreadsheet.
2.00, 2.30, 4.00, 5.00, 5.80, 5.70, 5.60, 6.00, 5.70, 5.20, 5.80, 5.00, 4.70, 4.00, 3.60, 3.15, 3.00, 2.70, 2.10, 2.15, 2.00, 1.90, 1.60, 1.40, 1.60, 1.00, 1.20, 1.00, 0.90, 0.80, 0.60, 0.55, 0.50, 0.50, 0.60, 0.15, 0.05, 0.05, 0.05, 0.05, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
Cheers
That example was for a completely different and specific purpose and if you read the followup you'll see why...
You are misreading the graph - you need to consider the area under the graph (only around 10% at 3rd month)?, and that would no doubt be very bias toward the F5 loans which have a default rate of 15.38%.
You are really confusing the discussion - try the given values for say an E3 loan at 35.33% annual interest and an annual default rate of 4.11% per year...and subtract the tax from 8%...then compare (still not a fair comparison as you have principal and interest to re-invest which is significant)...