A spreadsheet that graphs IRR and cash flow

[thefirstbruce]

I have been developing a spreadsheet originally written by Peter Noakes and Catherine Saul, which I sourced from this forum.

I wanted to get some feedback, and would appreciate comments from anyone who gives it a run. I am still messing with it, and some of the things that need to be updated are:

- transfer and mortgage duty for some states.
- set out of data entry sheet. if you have 800x600 screen, you might have to zoom out to 85%.
- develop a meaningul algorithm of the association between CPI, interest rates, and capital gains; so as to run more realistic simulations.

I know it looks 'busy' and has flaws, so if you could keep your criticism constructive, I'd appreciate it.

Basically I am trying to develop something that will be useful as an educational tool for people, esp younger ones, who have difficulty with long term perspective and abstractions such as IRR. I am a visual kinda guy myself and I always understand things better when i can see them.

BTW, I accept that the Somer's PIA software is the way to go if you want to crunch the numbers more accurately. However, I am not trying to rewrite PIA, rather develop an educational graphical simulator.

There are several simulated scenarios on it now:
- real interest rate and inflation data for 1960-1990
- same for 1975-2005
- a flat interest rate of 7%
- a couple of randomized 5-8 year interest rate cycles .

The idea is to put data for a potential purchase into the blue fields on the calculator page, then use the performance graph page to choose a simulation, then vary the gross capital gain. All this will be developed if/when I get the time.

You can download it from www.tekserv.com.au/bruce/IP1.zip
and I shall be putting revisions up from time to time.

Ta
Bruce

 

[quiggles]

TFB,

Don't have time ATM to play with the spreadsheet but I did want to thank you for doing this. It is this sort of contribution that helps both Somersoft and the broader investing community - and that's without judging its end success.

Regards and kudos,

Quiggles.

 

[thefirstbruce]

Quiggles, thanks. BTW, i just picked up a few more faults and have corrected them in the last 15 minutes. If anyone wants to test it, make sure you download just before doing so. I only developed the graphical simulator today after a long week. And it is bound to have created some unintended glitches.

I'll try and keep revising the edition no. so you can keep track.

 

[cmpalmer]

I am having a problem downloading. It says its not a valid archive.


Any ideas?

Chris

 

[duncan_m]

Any ideas?

Chris

For some reason its actually a .RAR archive.. but its named .ZIP

Rename it to a IP1.RAR and provided you have something like Winrar you'll be able to open it.. Unlikely that you'll have it.. hopefully Bruce doesnt mind me reposting it properly zipped here until he resolves it?


edit : original zip now works, attachment removed.

 

[cmpalmer]

Thanks that did it. I happen to have Win RAR on my computer.

 

[thefirstbruce]

Thanks Duncan,

I have only just cottoned onto that myself via a friend's computer.
I have been using a batch file to get winrar to zip and upload.
The end product as you say calls itself a zip file so I wasn't aware there was an issue as it has opened fine when I pull it back off the server onto my computer.

Will review the winrar script when I get home later today.

Thanks again and sorry about the hassle.

Cheers
Bruce

 

[thefirstbruce]

I have just sorted out the winrar zip incompatibility problem, so the original link in my first post should be fine now. Sorry about the hassle.

After messing with winrar's command line extensions and reading their documentation, I could not find a way to get full zip compatibility from their 'zip' creation module, so had to go back to using winzip. But winzip have separated out the command line functionality into a separate module which needs to be downloaded. Gee things are tough now if you want to automate via batch scripts! I could have easily left the file in raw xls format, but that compromises things when uploading it regularly, and is slow to download via 56k link. Or I could have made a self extracting exe, but firewalls and antivirus software goes crazy when you download those. If anyone knows better, please fill me in.


Duncan, could I ask you to remove the attachment you posted to Somersoft at some stage so there isn't an older version floating around.

 

[geoffw]

Bruce

You do have a macro in one on the worksheets, which triggers warnings for certain security levels. The macro is in Sheet3 (input). Do you know how to remove it?

 

[thefirstbruce]

Bruce

You do have a macro in one on the worksheets, which triggers warnings for certain security levels. The macro is in Sheet3 (input). Do you know how to remove it?

Geoff, I'd like to get rid of the macro. Peter Noake might have put it in, as I didn't. However when I check macros for the workbook, none are listed. If you can help, I'd appreciate it. BTW, I have protection enabled, but there are no passwords. It is just there to stop changing formulas inadvertantly.

PS I have the latest Norton Antivirus updates, so assume there's no nefarious macros in the spreadsheet.

 

[thefirstbruce]

Geoff,

I found an explanation for the macro alert, and have cleaned up the workbook accordingly, and uploaded.

 

[geoffw]

Yep, that's cleaned up.

 

[thefirstbruce]

Have tweaked the spreadsheet a little more today and would be interested in what anyone thinks might make it more educational or useful.

www.tekserv.com.au/bruce/IP1.zip 80KB

I'd like to be able to do realistic simulations. adjusting in line with economic outlook -inflation, interest rates, rent, capital gains above inflation, capital expenditures.

Does anyone use pre tax equivalent IRR?

 

[BBarnes]

Hello, TFB,
May I congratulate you, and thank you, for your efforts in amending this program. I have to thank so many others, also, for their spreadsheets...both simple and complex.
TFB, may I ask with the utmost sincerity, what percentage of investors on this forum are able to fully understand, and comprehend, and fully utilize, this complete, yet, to me, complex spreadsheet?
Let me enlarge on my question. Years ago, whilst well into my 50's and prior to my first investment, I purchased the PIA software. I was able to use it in it's simplest form, and found it a great tool to show me some facts and figures about investing in property. After investing in my first property, using 'Uncle Steve' and his Cashbond, and his strategies, I found I was unable to incorporate all this in my PIA software. Ian was most helpful in his advice to me, but I still couldn't get my head around how to do it.....and it was all to do with my inability to comprehend....nothing to do with the software. So I gave up, to avoid any embarrassment.
I put it all down to age, and to my personal chagrin, my valued software remains untouched. Since then I have gathered several small spreadsheets from contributors to this forum, and InvestEd, and use them as my 'bible'.
But my question still remains........am I in the minority? Do most serious property investors on this forum use this software, and other complex works, and fully understand it? And, in fact, are these pieces of software not as difficult as they appear to me?
I think I'm getting old !!
But would love some feedback......
Thanks to all,
Bill

 

[thefirstbruce]

Hi Bill

I totally agree the spreadsheet input sheet looks complex on first view.
If I go ahead and spend more time on it, I'd hide a lot of the detail and set it out in a more intuitive flowing manner. For the time being i have just built on the original layout, putting in some meaningful yields, breakeven points, Steve's rental reality stuff, CoCR, IRR, equity calcs, updating tables.

What I really want to get to though is the simulation thing. I think the powerful lesson I learnt when I first adjusted capital gains on the graph for various scenarios is that IP holding time reduces the variance of IRR. It is a powerful thing to see visually, and drives home the Somer's philosophy that it is time in the market, not timing, that counts.

I agree that even PIA software has its limitations, just like all software. And that is why I think it better we have the 'understanding' of basic investment and financial concepts, rather than just blindly entering data into fields, without being able to see how that data is processed.

I have been more successful teaching a couple of teenagers about NPV, compounding, inflation, and IRR via the spreadsheet graph then any other way. And it is these concepts that I want to simplify.

Bill, I'd be interested in seeing any spreadsheets you find helpful. I know Steve posted one here a few months ago that was well set out.

 

[redwing]

Reviq ?

TFB..Have you had a look at REVIQ as well?

I had a free trial version recently and liked the fact that you could play around with the projections..the complete package looks pretty awesome..

Does anyone know much about it or have much experience with it?

REDWING

 

[thefirstbruce]

Hey thanks for that Red. Will check their website tonight. Great concept from what I see so far, esp the data download thing.

 

[ArJay]

Oh this rocks......

Cheers TFB, you've obviously put in many hours into this "little" project. Kudos, kudos, kudos.....

ArJay

 

[ArJay]

Just a quick question...

Having a hard time getting my head around the "Real Equity" part of the sheet. By the looks of things it's being calculated by multiplying the previous years equity by the "Real Capital Growth Rate". This means that it's only compounding upon it's own value and not the overall value increase of the property in that year. Is that right, or am I completely not understanding the definition "Real Equity"?

I'm hardly a wiz at this stuff so please correct me if I'm missing something.

Thanks again...
ArJay

PS- also having probs with the NPV on the sheet. Is it supposed to be blank?

 

[thefirstbruce]

Arjay Thanks a lot for the feedback. You are right, there was an error in the Real Equity analysis. . I am still trying to confirm how real equity is calculated correctly. I have just updated with FV=PV*(1+i)^n, where PV is the previous month's Equity value, and 'i' is real capital growth rate (gross growth less inflaion). On the graph, it still looks like it isn't behaving as sensitively as it should though.

Further, I am also trying to get some hellp with NPV. Excel's calculation method leaves room for ambiguity, so am asking some excel gurus about it. There are no passwords on the file, if you want to mess with things yourself.

Thanks for letting me know. It is hard to see these things when you have your head drilled down into the detail for a week. Working out the yields and breakeven points took a lot of nutting out too.

I've posted the latest copy with changes to date.