A spreadsheet that graphs IRR and cash flow

[thefirstbruce]

Arjay, I have now established the following:


Real Rate of Cost of Borrowing
= nominal interest rate / inflation

= (1+Rate)/(1+Inf)-1


Real Growth Rate
= (1+nominal gross growth rate) / (1+inflation growth) - 1



Real Property Value Growth Rate
= (1 + Gross Growth Rate) / (1 + Inflation Rate) - 1
Previously, I was using 1+ (growth rate-inflation rate)



NPV can be calculated on nominal or real rates,and I still need some advice on which is best to use. If one is making cash flows with inflation affected dollars, then it is reasonable that those figures factor out inflation.

I am also pondering whether the outstanding loan principal should be held constant or devalued in today's $ terms.

 

[ArJay]

Hey TFB...

Ok I get ya...sounds more applicable. Has it been updated it on your sheet as yet? Will be interesting to see the graphics after it's been completed...

Funny that you should talk about the outstanding loan principle be devalued as I was just thinking the same thing this morn. It is an important part investing lore and a critical aspect to a buy and hold strategy. Would be an interesting graphic as well perhaps (not meaning to complicate things)?

As far as the NVP...yeh I'm prob more on the side of adjusting for inflation to get a better perspective of it's relation to the overall "picture".

Once again TFB....this is a brilliant tool from both an investment and educational point of view. I, like yourself, am more of a "visual" type person when it comes to this kind of analysis stuff. Have already learnt so much just being able to play with the variables and seeing their results represented graphically.

We should be grateful for you just putting it out there for us all to use.

ArJay

 

[thefirstbruce]

Arjay, I have struck an issue with the new formulas in that they make the raw data variance huge, which makes it difficult to plot graphically without applying a normalizing data transformation, such as expression of cash flow and real equity as natural logs. However, natural logs can't be applied to negative numbers. I'll have to explore an appropriate transformation.

In the meantime, I have updated the equations in the backend. But the graphs need more thought. I just uploaded now.

Thanks for kudos. I am realizing more and more how important it is to understand 'real' compounding growth as opposed to nominal. It is a hard thing to visualize in the mind's eye, and the whole inflation thing confuses most people, including me, when trying to establish real return and growth.

And I think the banks and financial advisors have exploited a lot of people by talking in nominal terms, rather than ''real' terms. My end goal is to cut through the delusion of an apparent great investment before inflation and even tax are considered. If the finance industry won't do it, it is up to the consumers to look after ourselves.

 

[thefirstbruce]

Arjay, have just updated and u/l the graphs. Had to separate them out until I can work out a transformation.

I have to get back to clinic for the rest of the arvo.

If you pick up any problems let me now.

 

[ArJay]

Thanks TFB...
Works great!!!

Perhaps the IRR, Pre-Tax CF, and IR could be combined into a single graphic and the Real Equity data left as a stand alone.

ArJay

eg......

 

[thefirstbruce]

Hi A,

Yes I think your suggestion works re 2 graphs. Which raises one of the limitations of Excel. You can manip data to logs, but you can't adjust Y axis formatting into original units....any ideas????

Further, you can;t get a primary and secondary Y axis label. Which would be great for 30 year displays....

BTW, I should mention I am working off Excel 2002 on the notebook at home. I was at one of my offices this arvo with Office 2003 and noted the graph data series labels weren't showing. What are you getting?

If you or anyone else has time to wade through a first year uni stats text, I'd appreciate advice re transformations/normalization of data inc. negative integers....

Cheers
Bruce

 

[thefirstbruce]

Here's a screen shot of the latest graphical frontend of the spreadsheet.

I've done away with the historical scenarios and added options to adjust most relevant variables. And have graphs for NPV, IRR, Real Equity, and pre tax cash flow.

You can download it at the original link
www.tekserv.com.au/bruce/IP1.zip
or link to it directly at
www.tekserv.com.au/bruce/IP1.xls

I note some computers don't seem to be displaying graph plot labels. Am still interested in feedback on how to improve it as an educational or analysis aid. Eventually, I'll add realistic cyclic patterns, rather than the current flat line interest, growth, and CPI structure.

 

[ArJay]

Hey TFB...

It's me, back to harass you again

New performance graph sheet looks really great (NVP etc). The ability to manipulate the variables without having to toggle back and forth between sheets really adds to it now as well. Think what you said about adding realistic cyclic patterns is a good direction though. That way you can get a bit better idea about the variable rates versus fixed, and see a bit better how the "real world" might look .Hey also...was the salary section of the input sheet supposed to be adjusted for inflation? Looks like it's referencing a blank cell on the CPI row.....?

I made a few personal mods to your last version just to get a better idea of the total effects of inflation (as we mentioned earlier). Looks a bit busy but still quite interesting. I really enjoy tinkering after someone like your guru self has done all the really tough brainwork.

Take care and cheers again.
ArJay

 

[thefirstbruce]

Arjay, yes you are right that the salary calcs were out, obviously a result of the heavy revisions. I have fixed that now and the dual income tax deduction looks ok.

Yes, the input sheet is looking busy. It will necessary to put in a clean front end for data entry, and hide the calculations sheet.

I have revised the NPV calcs as well, and been reading up on NPV and IRR. It is actually very complicated to run these calcs with scientific rigour. Information from business schools I am reading states most calcs of these two indicators are not done properly because inflation is not applied to all variables effecting outcomes. i.e. interest on loan (as you and I acknowledged). Therefore, nominal and real values are commonly intermixed, rendering results quite inaccurate.

IRR and NPV are actually very controversial in company valuation. Further, there are little acknowledged paradoxes with IRR, in that if more expenses occur towards the end of an investment than in the beginning, a higher IRR figure indicates a worse investment. I can imagine this might be an issue in property investment if one was to do a significant repairs a year of so before selling a house. Here's a tutorial on the matter:
http://hadm.sph.sc.edu/COURSES/ECON/invest/invest.html

Applying cyclic interest rate and inflation simulations will also take major overhauls of the input sheet structure. Anyway, I have some ideas on where to go with it, but it all takes time. Things could be simplified considerably by throwing out the detail for expenses, and just using algebraic relationships. However, this requires a bit of modelling, then the algebra to derive formulae that are robust across a range of realistic scenarios. And I don't have the maths experience to churn this stuff quickly. An experienced financial programmer could whip up the design for this in an afternoon, and write it in a couple of days.

Your modification of the graphs is interesting, and I'll keep it in mind down the track. I trust you understand real equity = inflation adjusted equity. Feel free to mess with it anyway you want. I don't claim ownership of the spreadsheet (Peter and Sandra gave birth to it), but have just one goal to produce a valid graphical simulator.

Must get to work.

 

[thefirstbruce]

AJ, have now added some simulation capability by adjustment of rent, interest, and growth in 2 year blocks.

 

[LearningMan]

tfb - where is the latest revised file so we may take a look at ?

Thanks !

 

[thefirstbruce]

same link as above.

 

[ArJay]

Jeeze....I don't know what to say....this is the brilliant. I thought the first one was really, really good....! Nothing to add TFB.....A+ in my book.

One thing that's interesting, the other night I added a series to what is now your cashflow chart that showed "inflation adjusted after-tax cashflow". Was really interesting to see just how much (or little) the cashflow from the property was in todays terms further on down the investment timeline. Really concreted in my mind just how important CG is to the portfolio.....learning, learning....

Thanks again mate...
ArJay

 

[thefirstbruce]

Hi Arjay,

Thanks again. My excel skills aren't anything special (& VBA non existent), so I am sure there are ways to improve the simulator in appearance and realism.

Plus there are many things that need to be considered still such as factoring into IRR, selling costs, in whichever year.

Just a couple of points about the graph

- The toggles only work when the boxes to the right are ticked. Unticking them clears the simulation adjustments.

- Remember that CPI is calculated as a function of interest rates. In my analysis, their relationship has been reasonable in the last 8 years, averaging 3.8%. Hence when you adjust interest rates at the top, you also pull up inflation. Interest Rate (hence Inflation) elevations will therefore lower real equity and cash flow. However, in times of a boom, you will have growth running higher than inflation/interest rates, as we have just seen 2001-2003. Anyway, the point is to remember to adjust these settings mindfully.

- The toggles cannot adjust original settings negatively. Therefore it is advisable to set growth close to CPI, then use toggles to adjust growth upwards in boom years. Of course, that's when property isn't going down in value.

- finally, note well that at present, when you make a toggle adjustment, it raises the figures of a two year block. I did this so one didn't have to go through and do 30 adjustments x 3. This should allow a reasonable simulation.


It has been an interesting exercise to graph changes. I have learnt a few things myself.

1.
It becomes really apparent why the Somers advocate a time in the market approach, as time evens the effect of adversity, on IRR.

2.
However, if one wants maximum return, and has other options for strong investment performance, the IRR plot can help one get out when performance has been strongest, and falls away or is flat after that point.

3.
Plotting real equity shows how talking in nominal equity terms masks the true strength or weakness of an investment. But just about everyone talks in nominal terms. If you talk in real terms, it shows just how critical timing is, and the significance of booms.

4.
If you simulate a boom, you see how IRR and NPV fall away after, until the next boom comes along (whenever that is). This justifies selling at the top of a boom, but only if you have a stronger investment to get into, or use the added equity to leverage into a stronger investment. Of course, the other consideration regarding booms, is that one should consider the yield based on the new valuation; which is obviously a lot weaker until rents can be raised. In this instance, it may be better to sell and chase the growth and higher yield in another investment.


Regarding your comments on inflation adjusted cash flow, I am sensing it might be easier not to use an inflation rate in the calculations, but rather to subtract inflation from growth and interest. (i.e. use real growth and real cost of borrowing) This way, the figures stay in today's dollar terms. And one shouldn't have to go through and add/subtract inflation to/from everything.

Thanks again for feedback Arjay. It has motivated me to plug away at it.

 

[House_Keeper]

Thanks for that Bruce.

That spreadsheet is just excellent. Very comprehensive and nicely laid out.

I'll be using it for the analysis of my next property purchase.

Thanks for this excellent effort.

Cheers,

 

[thefirstbruce]

Thanks House Keeper.

Keep in mind the spreadsheet is still under development.

Plus the more I go into it, the more I see needs could be done
- including medicare levy in tax
- factoring in selling costs in 'what if' scenarios.
- provding breakeven analysis for each year, not just year 1.
- developing internal rate of return to deal with 100% borrowed funds or equity from LOC.


You might consider the Somer's PIA software, as it is commercial and kept up to date. The spreadsheet should just be considered an eyeballer and educational simulator for the time being.

If you have any suggestions on improving usability let me know.

 

[thefirstbruce]

For those of you who think a picture is worth a thousand words, here's the mother of all resources in graphing Australian and world economic performance. I have been reading a lot in the last few weeks, and am continuing to be really impressed with the RBA's website offerings.

This pdf is 2MB but it is the most comprehensive and current collection of economic data I have come across.

http://www.rba.gov.au/ChartPack/graphical_summary.pdf

 

[ggumpshots]

I am not sure where you are on this project but its a great concept to visualise .
At the moment it is definetly not user friendly and having sat down to study it I understand whay you are doing. Your target market may not be willing to do this. ie sit down for a few minutes and work things out

I note that when interset rates are low.as in what I pay in Japan.then CPI becomes negative.!!
Output as a bar chart or pie chart mightbe worth considering
cheers
great work

 

[thefirstbruce]

Gump, thanks for feedback.

Inflation issue
Currently, I roughly measure inflation as a dependent of interest rates. I am using a 4.4% differential between the two.

For the last 10years, CPI in australia has averaged a 4.8% differential.
For the last 5 years, a 3.6% differential.
For the last 3 years, a 4.3% differential.

The relationship is obviously not linear at top and bottom, but Pearson's coefficient is still strong.

Eventually I'll include in the appendix, assumptions made. To date I haven't spent time considering a formula that is sensitive to rate/CPI differentials at each extreme.

Regarding Japan, I note you have had negative CPI changes, or deflation, since 1999; though the last 12 months looks like it might be flat.
http://www.stat.go.jp/english/data/cpi/158c.htm
I don't have time to check your interest rates up there, but would be interested in the differential between rates, growth, and CPI.


Complexity of Use
Re usability, I agree it tries to meet two conflicting expectations- comprehensive detail for accurate cash flow projections etc, and an easy to use simulator.

I have considered many ways to simplify simulator usage.
- just express in/out, and running costs as percentage of purchase price, and do away with all the options to enter these separately.
- hide the backend altogether and add more toggles for key variables, such as wage, IRR pre and post actual sale.

These things would make the simulator more helpful in addressing the issues we all have:
- is it better to sell or hold
- what if rates, rent, inflation increase,
- what if my income drops, such as taking a holiday when self employed.
- am I better off selling this year then next year
etc


I appreciate your input. If you have more opinions on how to improve it, let me know.

 

[lowb]

I was searching for an excel file and found this...

Its an old post but this excel file is great!
What I;m doing is, before buying my IP, I enter the price of the property and change it... and see how other values are effected