Somersoft Spreadsheet Collection
- Thread starter JamesGG
- Start date
Hi All,
First, bloody good thread this. Somersoft is such a good resource so I thought I'd try and give my little bit back.
Enjoy.
blw101
thanks for sharing
cheers
Hi All,
Shamelessly
That is a fantastic effort blw101! I really think you have made some great improvements.
I have a question on your IRR calculation on the "Portfolio" tab. I'm really trying to get my head around IRR and interested in the many approaches people take.
When calculating the Equity IRR for 5yrs, why do you subtract the Deposit from the Total Equity (cell H121) yet include it as part of the IRR calculation in cell B121?
Thanks!
Hi Paramount,
I posted a Townhouse feasibility spreadsheet in this thread a couple of years ago. You should be able to find it if you go back through the posts.
Cheers,
Bazza
Thank you for this spreadsheet. This is closer to the type of spreadsheet I was looking for. Something that allows me to analyse a property on its own as well as part of a complete portfolio.
One question - If I only have one income, how do I use the spreadsheet. I tried putting $0 or $1 in the Income B field and that then starts throwing #VALUE and #N/A errors everywhere. It seems the minimum amount I need to enter in Income B is $10,915.
Cheers,
MrHyde
One question - If I only have one income, how do I use the spreadsheet. I tried putting $0 or $1 in the Income B field and that then starts throwing #VALUE and #N/A errors everywhere. It seems the minimum amount I need to enter in Income B is $10,915.
Cheers,
MrHyde
Hi All,
First, bloody good thread this. Somersoft is such a good resource so I thought I'd try and give my little bit back.
Shamelessly
An early warning, it is pretty complex, but quite comprehensive.
First sheet (Portfolio) is where consolidation occurs, and also shows cashflows, including any tax implications based on wage income (assuming you hold properties in your own name).
Second sheet (PropList) is a simple property summary to update every now and then.
Third Sheet (PPOR) is where you can enter all details for your PPOR. PPOR must go here as costs are excluded from tax calcs. Living expenses are also put in here.
Each sheet between Prop> and <Prop represent each IP you hold (or are planning on purchasing). These are what get consolidated on the first sheet.
Sheets between DD> and <DD are extra sheets for properties you are looking at (or are in due diligence)
The trick is to setup an IP between the DD sheets to see how it performs in its own right. Once it is setup, drag the whole sheet across so it sits between the Prop> and <prop then look at the Portfolio sheet to see the effect your new IP would have on your portfolio.
Stamp duty rates are for NSW, but there are tables that can be changed on each individual IP sheet. Current income tax scales are on the Portfolio sheet.
There's a (crude) commercial property sheet setup as well which calculates future value based on income and cap rates for those commercial folk. (I'll stress it's crude, but does the job somewhat)
Hmm, what else...oh of course, only enter information in coloured cells, everything else is a calculation. (You'll see what I mean)
Enjoy.
* Disclaimer - this should not be used as professional advice. It is also being posted with the intention of helping all somersoft members who have provided me with so much information, so copy it and give it to other people, but dont go trying to sell it to anyone.
@ Mr Hyde
Good pickup. Set the second salary to $1 and then change the salary allocation (Cell B 79) to 100%.
@ Paisley, well, I think that's just a plain old mistake there, missed it as the deposit amount was small and didnt really affect the return.
Might just have to remove the deposit from the calcs in H121, M122 and R123.
Hope this helps.
Good pickup. Set the second salary to $1 and then change the salary allocation (Cell B 79) to 100%.
@ Paisley, well, I think that's just a plain old mistake there, missed it as the deposit amount was small and didnt really affect the return.
Might just have to remove the deposit from the calcs in H121, M122 and R123.
Hope this helps.
Great spreadsheet blw101... i'm just trying to get my head around it all. So once the IP 1 sheet is completed, you go back to the Portfolio sheet to see the results... which cell gives you the final after tax cashflow? Is it cell C103 ?
The ppor sheet has thrown me off a little, do the ppor figures influence the IP cashflow calculations?
I'm assuming C103/12 is the after tax monthly cost of IP 1?
The ppor sheet has thrown me off a little, do the ppor figures influence the IP cashflow calculations?
I'm assuming C103/12 is the after tax monthly cost of IP 1?
Hi Sluggo,
Let's see if this explanation cuts it:
In the portfolio sheet.
Line 103 is cashflow you have left after all salary, investment, tax, ppor and living expenses have been deducted.
If you just want to see your after tax investment cashflow, take the sum of lines 99 and 100 (and yes divide by 12 if you wish to get a monthly number).
PPOR.
The PPOR figures don't affect the investment cashflows in the portfolio sheet. The entire contents of the PPOR are summarised in "Portfolio" in lines 101 and 102.
What the PPOR information DOES do is affect the LVR calculations in lines 24-32, which of course is crucial for your next purchase.
It's also handy to have there if you eventually convert your current PPOR into an investment property.
Cheers,
BLW
Let's see if this explanation cuts it:
In the portfolio sheet.
Line 103 is cashflow you have left after all salary, investment, tax, ppor and living expenses have been deducted.
If you just want to see your after tax investment cashflow, take the sum of lines 99 and 100 (and yes divide by 12 if you wish to get a monthly number).
PPOR.
The PPOR figures don't affect the investment cashflows in the portfolio sheet. The entire contents of the PPOR are summarised in "Portfolio" in lines 101 and 102.
What the PPOR information DOES do is affect the LVR calculations in lines 24-32, which of course is crucial for your next purchase.
It's also handy to have there if you eventually convert your current PPOR into an investment property.
Cheers,
BLW
Just wondering if anyone uses a spreadsheet to either track IP maintenance issues and/or preventive ongoing maintenance issues.
I have had a look around the net and found a few ideas, though would like to see how some of the somersoft community handles / tracks maintenance on their investment properties.
Thanks
I have had a look around the net and found a few ideas, though would like to see how some of the somersoft community handles / tracks maintenance on their investment properties.
Thanks
My PM's just go ahead and organise quotes the work to be done and then consult me as to which quote I want to go with.
They usually flick me an email with the quotes attached.
Any external maintenance is organised & carried out by the strata managers inline with the body corporate maintenance plans that are in place and have already been budgeted for as per body corp fees.
Do you mind me asking what role the PM plays when unsatisfactory work is carried out, or a warranty claim needs to be made for faulty products. Do the PMs actually check the work has been carried out satisfactorily?
Yes my PM's organise warranty claims and/or unsatisfactory work to be carried out.
Great Thread
great thread guys been looking for a thread like this for a while.
great thread guys been looking for a thread like this for a while.
Last edited:
Hi,
I have downloaded cashflow project.zip but its protected. I have tried to unprotect it but its prompting for password: Does any one have password or work around?
Regards
TV
Super Model
Hi,
1st post here but have read a lot of stuff on the site.
Went through the spreadsheets you guys have put up. There are some pretty useful calculators here. IP Calculator seems to do a decent job.
Best investment property calculator I’ve found is from my money calculator. Not sure if I can post here because of copyright. But it’s free and can be found at http://www.mymoneycalculator.com.au/index.html
I believe a calculator there does all of that and more http://www.mymoneycalculator.com.au/mymoney-calculator.html
Hi,
1st post here but have read a lot of stuff on the site.
Went through the spreadsheets you guys have put up. There are some pretty useful calculators here. IP Calculator seems to do a decent job.
Best investment property calculator I’ve found is from my money calculator. Not sure if I can post here because of copyright. But it’s free and can be found at http://www.mymoneycalculator.com.au/index.html
I believe a calculator there does all of that and more http://www.mymoneycalculator.com.au/mymoney-calculator.html
Jeez, testing my memory there. I just downloaded it again and found that the blue cells are still workable and no password is needed to open or operate the spreadsheet unless you were planning to edit something?
And looking at it now with the benefit of hindsight... it is not one of the better tools on this thread
. Seriously, don't stress if you can't get it to work; there's much better stuff offered by other members in here anyway!!
