Calculating Yield and general Excel trickery

Hi All

I'm new to this forum, it looks really good though with a wealth of info. I am not so sure I am in the right forum section though so feel free to throw things at me.

Anyway - I get enjoy playing with excel. At the moment I am working on something to determine offer price based on expected rent, required yield, deposit size, loan interest rates etc.

My underlying strategy:

1. Find an areas expected not to drop in capital value.
2. Find properties in those areas that:
- provide a yield sufficient to cover loan repayment AND meet a 'minimum acceptable yield' figure
- provide mid to longer term options to subdivide / develop etc
3. Buy them at a good price using a combo of debt / equity
4. rent them out
5. Overtime look into subdivision / development / revaluation etc as I gain experience

My spreadsheet takes expected rent as an input and derives an offer amount that will meet the requirements of my strategy (covering repayments etc).

A few ponderings:

- the yield on the property prices often seems crazily low to me, well under 5%! I guess this is due to expectation of capital gain.
- The 'actual' yield I think should be based on my deposit amount, the rent recieved and the cost of servicing the loan. I believe when making this calculation I should base it only on interest only payments. This figure will be more susceptible to changing interest rates, rent fluctuations and will be more useful to track performance.

My questions are:
Do be people typically pay attention to this 'actual' yield figure, what kind of numbers do they look for?
Is paying interest only a common approach, with any extra into an offset?

My spreadsheet oversimplifies things - eg assumption about neutral capital growth, no maintenance costs, no allowance for mortgage insurance, changes in 'actual' yield if the principal is paid off. I will include some of these over time, is there anything else people should think i should include to properly model reality?

Currently I have these fields:

Viable Price: the offer price that provides the set yield on house value based on the rent - this needs to include all costs like stamp duty etc
Weekly rent: based on what the agent says they can get for it
Annual rent: Weekly rent times 52 weeks in a year
Yield (property value based) Set this as required annual yield for the property value at purchase
Initial Equity The percent deposit
Deposit Deposit size based on initial equity percentage and the offer price
Yield (Gross, Deposit) The yield you are getting on the deposit amount based on the annual rent amount - doesn' exclude interest payments
Loan Amount The amount of loan needed, offer price minus deposit
Repayments per year repayment frequency
Loan Term (years) how many years until the loan is paid off (this asumes principal and interest obviously)
Loan interest rate the interest rate of the loan
Total Interest total interest over course of loan
Weekly Interest the interest you pay per week, excluding the principal
Interest + principal total interest + principal payments over the course of the loan
Weekly repayments weekly principal + interest repayments
Annual Repayments annual interest + principal repayments
Annual Interest Repayments annual interest only payments
Annual Net (after interest before principal) annual rent - annual interest payments, I think this is the best number to use to calculate the real return on the amount you have actually put in to the property?
Annual Yield net Annual net amount as a percentage of the deposit amount

If anyone else has some Excel tricks / questions feel free to bring them up as well, I am always keen to learn things and happy to give others a hand if I can.
Hi Db,

1. Using monthly amounts will be more useful than weekly amounts. Even if you are paid weekly, interest is paid monthly & usually rent too. Also, there are exactly 12 months in a calendar year but not exactly 52 weeks.
2. Don't rely on what the agent says a property will rent for - do your own research. Most sales agents are not property managers and are often just guessing about rental income. They also have an incentive to over-estimate :)
3. Don't expect to make the perfect investment purchase. You can only control and predict certain variables and over analysing can paralyse decision making.
4. Don't lose sight of the real world. Spreadsheets are great for financial analysis but there are plenty of important investment aspects that are difficult/impossible to include in a model.

Good luck!