yet 1 more property holdings spreadsheet

[jaycee]

According to this s/sheet using my figures, my LVR is 71.9%.. If I were to use LOC and rent only to pay for expense wihtout chipping in, assuming 5% cagpital growth, my LVR would go down to 71.288%... yay 0.6% of not much ! If I use don't property manager, I might be able to ge .83%, almost a whole number.

Good s/sheet, not much more to do to it - you can use it to look at current / future scenarios, and you can use it as a statement showing thee result of the financial year... and it's nic & easy to use too


Hehe, I just added my super fund in plac fo the last property. That makes me feel better when looking at the LVR - oh, I porbably gotta take 15%off right ?

 

[Rickardo]

Hi Rick

Thanks for your input.
Can you give me an example please using some $ figures?

I find it hard to understand the benefit of capturing such expenditure when you haven't done your tax return yet so you don't get an immediate benefit.

cheers

BV,

The reason i like to determine net property cashflow is because it allows you to calculate every dollar you've ever spent on a property. This can then be used to evaluate performance and calculate returns.

This is best done retrospectively - i do it for each property at the end of the FY after i've lodged my tax return, while the way you've structured yours is better for forecasting. I have a spreadsheet showing actual $ figures but in it's current form it's probably hard to follow for anyone but me - i will fix it up a little and post it soon.

 

[BV]

See if this revision clarifies in any way. I've only done the personal worksheet but easy enough to revise the lot.

WW
Thanks mate.
I had a look at your version and got frightened
I think I'll stick to the easy to understand version
What was the problem with the 2010/11 tax table?

 

[Rickardo]

WW,

How do i get the hidden worksheet for tax calcs back on your version? I always like to be able to access the data that the numbers are being calculated from

See if this revision clarifies in any way. I've only done the personal worksheet but easy enough to revise the lot.

Changes made include:
- set out
- fix mistake in tax table.
- delete hidden worksheet for tax calcs
- include performance of PPR.
- option for capital gain contribution
- other minor adjustments.

 

[WinstonWolfe]

WW
Thanks mate.
I had a look at your version and got frightened
I think I'll stick to the easy to understand version
What was the problem with the 2010/11 tax table?

BV, re tax, whoops sorry, I was confused by the xls being named 2010 and 5 of 6 of the tables on the tax worksheet being headed 2009/2010; but you've used the 2010/11 FY tax tables.

I think there was another issue on the smsf ws. Cell C40 should be -1087.
The way I understand it, the samuel street property is +CF and therefore increases tax liability. When designing xls, it's helpful to avoid entering negative numbers and be weary of them in formula.

Something else I did that you might consider is the combined worksheet just copies the cells from personal and smsf, so you don't have to re-enter all the data.

One of the issues doing an annual overview worksheet like this is that you have to manually account for changes to interest rates, rents, and expenses during the year. But better than nothing.



Rikardo, there's no hidden tax worksheet. The tax calcs are done by the the formula below and a named range for the tax table. The table is on the personal worksheet at cell AA1. Using one table makes it easier to update from year to year.

=Tax0-(VLOOKUP(Income0+B31,tax,2)
+VLOOKUP(Income0+B31,tax,3)*(Income0+B31-VLOOKUP(Income0+B31,tax,1)+1))

where
tax = named range for tax table.
Income0 = named range for sum of income excluding IP.
Tax0 = tax on Income0 (excludes IP).

The part of the formula starting with vlookup works out tax liability including IP, then subtracts that from the tax liability excluding IP. That difference is the tax credit or debit.

 

[Bolero]

BV,
how to change tax % for the cell B38? I'm afraid I'm not very good with exel

 

[BV]

BV,
how to change tax % for the cell B38? I'm afraid I'm not very good with exel

You don't have to edit cell B38, you just need to put in your salary in B35 and it will automatically calculate the taxable amount shown in B38.

Make sure you use the updated spreadsheet found here
http://www.somersoft.com/forums/showpost.php?p=711936&postcount=9

 

[dl2276]

Handy spreadsheet - thanks for sharing.

In relation to tax, this does not include Medicare Levy (1.5%). I am guessing that is relevant for most of us PAYG people ?

 

[BV]

Handy spreadsheet - thanks for sharing.

In relation to tax, this does not include Medicare Levy (1.5%). I am guessing that is relevant for most of us PAYG people ?

No it doesn't include any Medicare levy reduction as your taxable income drops.

I didn't think it was significant to worry about it.
Your Medicare levy will be reduced by $150 for every $10K reduction in your income so it isn't a big deal but I'll add it to the next update.

I'll also consolidate all the entries into 1 sheet as per Winston's recommendation.

 

[Edmond Dantes]

Nice one:

1) the values from building & plant depreciation, do you get them from QS report?
2) row 36, fixed interest, is this interest generated from a savings account, for example?

 

[BV]

Nice one:

1) the values from building & plant depreciation, do you get them from QS report?
2) row 36, fixed interest, is this interest generated from a savings account, for example?

1) yes, it will also be shown in your tax return
2) yes

 

[BV]

I'm attaching the latest version which includes savings in the Medicare levy due to our reduced tax.

In this version you only need to enter data once.

Remember to only enter your data in the highlighted Green cells of the PERSONAL sheet or the SMSF sheet

 

[Ski-bum]

Thanks for the updated version BV - just a few things I had to change (sorry to edit the spreadsheet again!):

1 - rental yield (B25/C25/D25 etc) calculated on current valuation instead of purchase price.

2 - portfolio LVR (B8/C8/D8 etc) should all be the same, so I just made C8/D8/E8 etc =B8.

Good spreadsheet and like the author acknowledgement in the last tab

 

[BV]

1 - rental yield (B25/C25/D25 etc) calculated on current valuation instead of purchase price.

The calculated yield in B25 etc is based on purchase price.
Are you saying that it should be calculated on the current valuation?

2 - portfolio LVR (B8/C8/D8 etc) should all be the same, so I just made C8/D8/E8 etc =B8

I fixed the error, my initial thought was to capture the LVR as the number of IP's increase so it will be different LVR in every column but I don't know if this is of value to anyone so I followed your advise and kept it simple.

 

[Ski-bum]

The calculated yield in B25 etc is based on purchase price.
Are you saying that it should be calculated on the current valuation?

I suppose it is an issue of contention - yield based on purchase price Vs current price.

I like to know based on current price, so that was easy enough to change for myself.

cheers

 

[BV]

I suppose it is an issue of contention - yield based on purchase price Vs current price.

I like to know based on current price, so that was easy enough to change for myself.

cheers

Ok its a personal choice.

I think it is better to monitor the rental yield based on purchase price because you can see what your rental return is for your initial outlay.

If you monitor the rental yield based on current valuation it will always look lower than it actually is.
This would bother me because I'd like to see the yield increase over time.

Cheers

 

[BV]

Here is the updated version

Fix: The PPOR loss was missing a minus sign and some +ve numbers were not showing in Green colour

 

[Edmond Dantes]

Here is the updated version

Fix: The PPOR loss was missing a minus sign and some +ve numbers were not showing in Green colour

thanks! just wondering, where are you using your Offset Account Balance Cell??

 

[BV]

thanks! just wondering, where are you using your Offset Account Balance Cell??

Is used in cell F11 where it offsets the PPOR loan balance.
So the interest is calculated on the loan difference (Main loan - offset) x current interest rate))

 

[JPS25]

Thanks very much BV this is great for first timers like ourselfs. We are looking at getting a second Ip shortly so great for putting in figures and seeing how they will work out so quick and simple.

keep up the good work

JPS25