- Joined
- 3/21/09
- Messages
- 2
- Points
- 11
Hi All
My name is Robert and this is my first post here. Firstly, great forum, so thanks for having me.
I have turned to this forum because i have a brain freeze with some (what I thought was relatively straight forward) excel spread sheeting. I did a search on financial modelling forums and this forum came up, so i thought i would give it a try. Hopefully you guys can help. It would be awesome if you could.
Ok, I have a tendency of being a little verbose when I am trying to explain something I fully don't understand, so I will ask that you bear with me. Thanks.
I will explain to you where i am stuck but firstly let me briefly introduce myself. My name is Joseph and I live in Melbourne Australia and im a commodity trader (technical analysis) full time and I also have a small property development company that i started a couple of years ago on my own and its now grown to slightly more than just a hobby. I will also preface this post by saying I am, by no means, looking for investors, therefore this is NOT spam. Anyway, i am playing around with some tables and formulas on excel 2007, which i am quite rusty on, and have been struggling to successfully complete a projection for the next 5 years.
Let me give you the variables and share with you what i am trying to achieve.
I currently own a property that is worth 500K AUD, and the loan is paid off. We'll call this UNIT A.
I am trying leverage off this 500K in equity in UNIT A. What I intend to do is buy another property for the same price, 500K. I intend to sub-divide it, so I will effectively be creating another piece of land with its own land title. This piece of land will be the former back yard of the initial purchase.
I need 700K from the bank to achieve this (500 [purchase price] + 200 [construction costs] ). What the banks will use as my equity will be the value of UNIT A (500K) plus the value of my new purchase, we'll call it HOUSE 1 (500K). This gives them $1,000,000 of equity as security and they will lend me up to 90% of that value - I only require 70%, so 700K. (I would like to maintain this Loan-To-Value Ratio (LVR) right throughout my development portfolio, so 70% against my total net equity at all times).
Once I purchase HOUSE 1 for 500K, I will subdivide it , sell it off at a loss for 400K (a loss, given I have halved the size of the land it sits on). So I am down 100K but I now have an empty block of land that I can put a unit on, we'll call it UNIT 1. I will pay construction costs of 200K to develop UNIT 1, which will then have a market value of 500K once completed. So the profit from this small piece of development will 200K (500K - 200K [construction] - 100K [loss on HOUSE 1]).
I do not intend to sell UNIT 1. I intend to keep it so I can leverage off the pre-tax equity in UNIT 1 (if I were to sell it, and crystallise a capital gain, I will have to pay substantial tax on the profit therefore my borrowing power will be much less compared to if I didn't sell it). So my strategy is that I will always keep the back unit and always sell the front house for cash-flow and debt reduction purposes. The rent I receive from this back unit should be able to substantially reduce the interest payments from the remaining outstanding loan.
So, initial loan of 700K (fully drawn) is now down to 300K (I offset the 400K from the sale of HOUSE 1 against the total loan outstanding, [700-400=300] )
The loan facility I have is a called a Line of Credit, and now that I have reduced it by 400K. What I would like to do at this point is to get the bank to reduce the 700K Line of Credit to a 350K Line of Credit. The reason am I doing this is because it is inline with my leverage strategy of maintaining 70% LVR at all times.
So now that means that UNIT A has a loan against it of 350K, against its initial value of 500K, so an LVR of 70% (350K/500K). And I now have a brand new (and already paid off) UNIT 1, also worth 500K that I would like to leverage off. And do exactly the same thing as above.
My current net equity position is now 700K. This is based on 1,050,000 - 350,000. (500K [UNIT A] + 500K [UNIT 1] + 50K [ remaining in my line of credit : 350 loan - 300K already drawn] LESS 350K loan facility)
Ok, so my net worth is now 700K after T1 (year 1). And I would like to do the same thing over and over again, if banks, the economy and my job permits, of course. The tricky parts comes in when I introduce a capital growth rate of 10% per annum (yes, yes, big assumption given current crisis, but over time, this is what it should be more or less). Also when I introduce an interest rate of 7% and an annual rental yield of 4% , its starts getting slightly complicated. So the rental income will not completely take care of interest on the loan. Say I have annual interest payment s (interest only) of 24.5K (7% of 350K) as above, and the rental yield on UNIT 1 is 20K (4% of 500K), that represents a net loss of ordinary income of 4.5K each year, which will need to be reflected in my net equity position at the end of each period. But now its gets a little more complicated due to the fact that I will have fixed an interest rate of 7% for the next 5 years, and my annual rent in 5 years time on UNIT A should be about 32K p.a (4% of UNIT 1's FV of 805K [500 x 1.1^5]). So this also needs to be reflected in my net equity position in five years time. What also needs to be reflected is the fact that in 5 years time UNIT 1, will have increased in value (net of loan) by 305K (assuming 10% compound annual growth rate). UNIT 2 will have increased in value by 236K in 4 years time, UNIT 3 would have increased in value by 165K in 3 years, UNIT 4 by 105K in 2 years... and so on and so forth.
Oh, I also need to reflect my very first purchase being UNIT A, that will also be appreciating in value as well as having a rental yield.
I also need to reflect a stamp duty of 5% on each purchase (this is probably the easiest of all of them
I am excluding some other things which are not important for this exercise, ie. No tax, a consistent capital growth rate, a consistent interest rate, rental yield etc
I am really stuck on how I would express all of the above in a presentable and neat way so that I can play around with the variables. Ie. Interest rate, capital growth rate, construction costs, rental yield etc etc. I know its possible but I am about 30% away from achieving. I am appealing to any excel / modelling wiz kids who ay be able to assist. If I am being a little unrealistic in finding someone who can assist then please let me know, and if you could point in the direction of someone who could help that would be greatly appreciated. Even if you cant help me , I still appreciate you taking the time to read up until this point, thank you.
In my spreadsheet, you will see the following headings and variables :
Using 500K fully paid off investment property as leverage for starting this project
Initial purchase price
Stamp duty 5% of purchase price
Purchase price plus stamp duty
Equity to be used
Add current purchase price of house I am proposing to buy
Total equity banks can you use to work out the loan-to-value ratio (LVR)
loan amount: LVR 70% (which the max LVR i want to have throughout all stages of development )
sale of front house (80% of initial purchase price)
remaining loan: subtract total amount from sale of front house from current loan facility
Effective purchase price of backyard ( [purchase price & stamp duty] minus sale of front house)
Construction & other associated costs for backyard unit: 40% of initial purchase price
Total costs: backyard purchase plus construction costs
Value of the developed unit after the sale of front house (initial purchase)
Pre tax equity created after construction that adds to the lending power when I go for another loan to fund my next development
Annual rental income from unit at 4%
Annual interest on outstanding loan at 7%
Difference between annual rent revenue and annual interest payment
Total Equity after T1,2,3,4,5
Accumulative Total Equity after T1,2,3,4,5
And I also have a table with the following :
Interest = 7%
Capital growth rate = 10%
Rental yield = 4%
Stamp duty = 5%
LVR = 70%
Sale of HOUSE **represented as a percentage of initial purchase = 80% (ie. If purchase was 500K, then subdivide it and sell it for 400K -- as above)
Construction costs represented as a percentage of initial purchase = 40% ie. If purchase was 500K, then construction costs will be 200K -- as above)
If you can help me with this it would be amazing. You have no idea how much I have been racking my brains about this spreadsheet, it was once a fun exercise but now I feel I need to get this completed so I can get my sanity back.
If anyone wants my email address its:
robert23041951@optusnet.com.au
Also, let me know if you like to take a stab at my problem and i can send the excel spreadsheet of what i have don't so far, like i said its about 70% complete
Cheers
My name is Robert and this is my first post here. Firstly, great forum, so thanks for having me.
I have turned to this forum because i have a brain freeze with some (what I thought was relatively straight forward) excel spread sheeting. I did a search on financial modelling forums and this forum came up, so i thought i would give it a try. Hopefully you guys can help. It would be awesome if you could.
Ok, I have a tendency of being a little verbose when I am trying to explain something I fully don't understand, so I will ask that you bear with me. Thanks.
I will explain to you where i am stuck but firstly let me briefly introduce myself. My name is Joseph and I live in Melbourne Australia and im a commodity trader (technical analysis) full time and I also have a small property development company that i started a couple of years ago on my own and its now grown to slightly more than just a hobby. I will also preface this post by saying I am, by no means, looking for investors, therefore this is NOT spam. Anyway, i am playing around with some tables and formulas on excel 2007, which i am quite rusty on, and have been struggling to successfully complete a projection for the next 5 years.
Let me give you the variables and share with you what i am trying to achieve.
I currently own a property that is worth 500K AUD, and the loan is paid off. We'll call this UNIT A.
I am trying leverage off this 500K in equity in UNIT A. What I intend to do is buy another property for the same price, 500K. I intend to sub-divide it, so I will effectively be creating another piece of land with its own land title. This piece of land will be the former back yard of the initial purchase.
I need 700K from the bank to achieve this (500 [purchase price] + 200 [construction costs] ). What the banks will use as my equity will be the value of UNIT A (500K) plus the value of my new purchase, we'll call it HOUSE 1 (500K). This gives them $1,000,000 of equity as security and they will lend me up to 90% of that value - I only require 70%, so 700K. (I would like to maintain this Loan-To-Value Ratio (LVR) right throughout my development portfolio, so 70% against my total net equity at all times).
Once I purchase HOUSE 1 for 500K, I will subdivide it , sell it off at a loss for 400K (a loss, given I have halved the size of the land it sits on). So I am down 100K but I now have an empty block of land that I can put a unit on, we'll call it UNIT 1. I will pay construction costs of 200K to develop UNIT 1, which will then have a market value of 500K once completed. So the profit from this small piece of development will 200K (500K - 200K [construction] - 100K [loss on HOUSE 1]).
I do not intend to sell UNIT 1. I intend to keep it so I can leverage off the pre-tax equity in UNIT 1 (if I were to sell it, and crystallise a capital gain, I will have to pay substantial tax on the profit therefore my borrowing power will be much less compared to if I didn't sell it). So my strategy is that I will always keep the back unit and always sell the front house for cash-flow and debt reduction purposes. The rent I receive from this back unit should be able to substantially reduce the interest payments from the remaining outstanding loan.
So, initial loan of 700K (fully drawn) is now down to 300K (I offset the 400K from the sale of HOUSE 1 against the total loan outstanding, [700-400=300] )
The loan facility I have is a called a Line of Credit, and now that I have reduced it by 400K. What I would like to do at this point is to get the bank to reduce the 700K Line of Credit to a 350K Line of Credit. The reason am I doing this is because it is inline with my leverage strategy of maintaining 70% LVR at all times.
So now that means that UNIT A has a loan against it of 350K, against its initial value of 500K, so an LVR of 70% (350K/500K). And I now have a brand new (and already paid off) UNIT 1, also worth 500K that I would like to leverage off. And do exactly the same thing as above.
My current net equity position is now 700K. This is based on 1,050,000 - 350,000. (500K [UNIT A] + 500K [UNIT 1] + 50K [ remaining in my line of credit : 350 loan - 300K already drawn] LESS 350K loan facility)
Ok, so my net worth is now 700K after T1 (year 1). And I would like to do the same thing over and over again, if banks, the economy and my job permits, of course. The tricky parts comes in when I introduce a capital growth rate of 10% per annum (yes, yes, big assumption given current crisis, but over time, this is what it should be more or less). Also when I introduce an interest rate of 7% and an annual rental yield of 4% , its starts getting slightly complicated. So the rental income will not completely take care of interest on the loan. Say I have annual interest payment s (interest only) of 24.5K (7% of 350K) as above, and the rental yield on UNIT 1 is 20K (4% of 500K), that represents a net loss of ordinary income of 4.5K each year, which will need to be reflected in my net equity position at the end of each period. But now its gets a little more complicated due to the fact that I will have fixed an interest rate of 7% for the next 5 years, and my annual rent in 5 years time on UNIT A should be about 32K p.a (4% of UNIT 1's FV of 805K [500 x 1.1^5]). So this also needs to be reflected in my net equity position in five years time. What also needs to be reflected is the fact that in 5 years time UNIT 1, will have increased in value (net of loan) by 305K (assuming 10% compound annual growth rate). UNIT 2 will have increased in value by 236K in 4 years time, UNIT 3 would have increased in value by 165K in 3 years, UNIT 4 by 105K in 2 years... and so on and so forth.
Oh, I also need to reflect my very first purchase being UNIT A, that will also be appreciating in value as well as having a rental yield.
I also need to reflect a stamp duty of 5% on each purchase (this is probably the easiest of all of them
I am excluding some other things which are not important for this exercise, ie. No tax, a consistent capital growth rate, a consistent interest rate, rental yield etc
I am really stuck on how I would express all of the above in a presentable and neat way so that I can play around with the variables. Ie. Interest rate, capital growth rate, construction costs, rental yield etc etc. I know its possible but I am about 30% away from achieving. I am appealing to any excel / modelling wiz kids who ay be able to assist. If I am being a little unrealistic in finding someone who can assist then please let me know, and if you could point in the direction of someone who could help that would be greatly appreciated. Even if you cant help me , I still appreciate you taking the time to read up until this point, thank you.
In my spreadsheet, you will see the following headings and variables :
Using 500K fully paid off investment property as leverage for starting this project
Initial purchase price
Stamp duty 5% of purchase price
Purchase price plus stamp duty
Equity to be used
Add current purchase price of house I am proposing to buy
Total equity banks can you use to work out the loan-to-value ratio (LVR)
loan amount: LVR 70% (which the max LVR i want to have throughout all stages of development )
sale of front house (80% of initial purchase price)
remaining loan: subtract total amount from sale of front house from current loan facility
Effective purchase price of backyard ( [purchase price & stamp duty] minus sale of front house)
Construction & other associated costs for backyard unit: 40% of initial purchase price
Total costs: backyard purchase plus construction costs
Value of the developed unit after the sale of front house (initial purchase)
Pre tax equity created after construction that adds to the lending power when I go for another loan to fund my next development
Annual rental income from unit at 4%
Annual interest on outstanding loan at 7%
Difference between annual rent revenue and annual interest payment
Total Equity after T1,2,3,4,5
Accumulative Total Equity after T1,2,3,4,5
And I also have a table with the following :
Interest = 7%
Capital growth rate = 10%
Rental yield = 4%
Stamp duty = 5%
LVR = 70%
Sale of HOUSE **represented as a percentage of initial purchase = 80% (ie. If purchase was 500K, then subdivide it and sell it for 400K -- as above)
Construction costs represented as a percentage of initial purchase = 40% ie. If purchase was 500K, then construction costs will be 200K -- as above)
If you can help me with this it would be amazing. You have no idea how much I have been racking my brains about this spreadsheet, it was once a fun exercise but now I feel I need to get this completed so I can get my sanity back.
If anyone wants my email address its:
robert23041951@optusnet.com.au
Also, let me know if you like to take a stab at my problem and i can send the excel spreadsheet of what i have don't so far, like i said its about 70% complete
Cheers
