This template enables users to calculate a comprehensive residential property investment forecast for any property investment or primary residence over any investment period between 1 month and 20 years. The functionality that has been included in the template is comparable to any property software solution - in fact, we guarantee that you will not find a more practical and comprehensive residential property forecast solution anywhere else. Users are required to specify input values for a comprehensive series of property variables and the template automatically calculates a comprehensive, accurate investment return forecast on an annual and cumulative basis.
This template is provided courtesy of Property Reality. The template forms part of the unique range of innovative residential property templates that are available on the Property Reality website (www.propertyreality.co.za). We highly recommend that you visit Property Reality in order to view samples of all the templates that are included in their comprehensive template subscription! If you are only interested in a residential property investment forecast template, you can buy this template by using our shopping cart.
Note: We highly recommend using this template in conjunction with Property Reality's unique buy to let and primary residence property investment templates. The property investment forecast template enables users to compile an accurate investment forecast and the buy to let and primary residence property investment templates enable users to compile an accurate analysis of the actual investment return that is achieved. By comparing the results that are produced by these templates, users are able to measure actual investment return against initial expectations.
The following sheets are included in this template:
Input - this sheet includes most of the input variable values that need to be specified in order to calculate an accurate property investment return forecast.
PrimeRate - this sheet includes monthly prime interest rates from January 2000 to December 2050. The interest rates that are entered on this sheet are included in a monthly amortization table after deducting a rate discount that is specified by the user.
AdHoc - this sheet facilitates including ad hoc bond repayments in the monthly amortization table on the MonthAmort sheet. All the ad hoc bond repayments that are entered on this sheet are automatically included in the monthly amortization table based on the monthly periods in column A.
MonthAmort - this sheet includes a monthly bond amortization table that incorporates variable monthly interest rates and ad hoc bond repayments. The monthly interest rates are determined by deducting the rate discount that is specified in cell F6 from the prime interest rates that are entered on the PrimeRate sheet. The ad hoc repayments that are entered on the AdHoc sheet are automatically included in the monthly amortization table.
CashFlow - this sheet includes a comprehensive property cash flow calculation that is based on the input variable values on the Input sheet and the monthly amortization table that is calculated on the MonthAmort sheet. The only user input that is required is specifying the forecasted annual capital growth rates.
Results - this sheet contains a comprehensive analysis of the forecasted property investment return. All the values on this sheet are automatically calculated based on the input values and calculations that are included on the other sheets. No user input is required on this sheet.
Variables - this sheet includes all the residential property calculation variables that have been incorporated into the template calculations. If any of these variables need to be amended, the appropriate values can simply be entered on this sheet and all the template calculations will be updated automatically.
User Input
This section of the instructions provides more information on the user input that is required in this template. Most of the user input fields are included on the Input sheet and we'll therefore start by providing guidance on each of the user input fields on this sheet.
Property & Bond Details
Users are required to enter the property purchase date, property purchase price, deposit amount and bond period in cells B5 to B8 on the Input sheet. The property purchase date determines the monthly or annual periods that are included on the CashFlow, Results and MonthAmort sheets. The property purchase price should be the full purchase price as per the deed of sale and the deposit amount should be the cash portion of the purchase price. The bond amount is calculated by deducting the deposit amount from the property purchase price and the bond period in years needs to be entered in cell B8.
The checkbox in cell C9 needs to be selected if the property is purchased by an entity that is registered for value added tax (VAT) purposes. If the property investor intends to renovate the property immediately after it has been acquired, the amount of the intended initial renovation cost should be entered in cell B10.
Transfer & Bond Costs
The initial transfer & bond costs in cell D12 to D17 are automatically calculated based on the property purchase price and bond amount (property purchase price less deposit amount) that is specified on the Input sheet and the transfer & bond cost calculation variables that are included on the Variables sheet. The costs that are calculated in this cell range are also automatically included in the cash flow and investment return calculations.
If you want to override the default costs that are calculated, simply select the appropriate list box in column C next to the calculation that you want to override and simply enter the amount that should be included in the cash flow and investment return calculations in column B.
Rent Income
The monthly forecasted rent income, occupancy rate and annual rent increase needs to be entered in the cell range from cell B19 to B21. The occupancy rate and annual rent increase needs to be entered as percentages. An occupancy rate of less than 100% can be specified if the property investor wants to make provision for months where the property will not be leased to anyone.
Example: If you want to provide for a property not being leased for one month out of a 3 year period, the occupancy rate can be calculated by dividing 35 months (36 months minus 1 month) by the full 36 month period and an occupancy rate of 97.2% should therefore be specified.
In some property investment scenarios, a rent subsidy is paid to the property investor for a fixed initial ownership period. If a rent subsidy is included in the purchase agreement, the appropriate monthly rent subsidy amount can be entered in cell B22 and the subsidy period in months can be entered in cell B23. If a rent subsidy is not included in the agreement, simply enter nil values in these two input cells.
Operational Expenses
The forecasted property management fees, rates, levies, repairs & maintenance costs, insurance premiums and other operational costs need to be entered in the cell range from cell B25 to cell B30. Property management fees should be entered as a percentage and are calculated by applying the specified percentage to the gross monthly rent income (before taking the occupancy rate into account). Annual estimates of the other operational costs should be entered in the other input cells.
The operational cost estimates are increased by the operational cost increase percentage that is specified in cell B31 for all subsequent investment periods. This means that the cost increase percentage is added to the forecasted cost of the previous period in order to calculate the operational costs for all the investment periods after the first year.
Selling Costs
The estimated selling costs that will be incurred when disposing of the property investment consist of agents commission, other selling costs and bond cancellation fees and need to be entered in the cell range from cell B33 to cell B35. Note that the estimated agents commission should be entered as a percentage that includes VAT.
Entity Details
In order to calculate an accurate property investment return forecast, the enterprise form should be selected from the list box in cell B37. The income tax and capital gains tax rates of individuals, corporate entities and trusts are not the same and it is therefore important that the enterprise form is taken into account when calculating the property investment return.
The effective income tax rate in cell B38 and the effective capital gains tax rate in cell B39 are automatically calculated based on the enterprise form that is selected from the list box in cell B37 and the income tax and capital gains tax rates that are included on the Variables sheet.
Other Input Cells
If the template is being used to calculate an investment return forecast for a primary residence, the checkbox in cell C41 needs to be selected. There are a number of capital gains tax exemptions that apply only to primary residences and this checkbox therefore needs to be selected in order to take these exemptions into account in calculating the appropriate capital gains tax amount that will be incurred when disposing of the property.
The template can be used to calculate a property investment return forecast over any investment period between 1 month and 20 years. The investment period in cell B42 should therefore be entered in months and only values between 1 and 240 will be accepted. The last month that is included in the investment period is displayed in cell D42.
The template calculation Results include a net present value (NPV) calculation which requires a discount rate to be specified in order to calculate the net present value of all the net annual cash flows that are included in the investment period. A discount rate equal to the average annual inflation rate can be used in order to calculate the net cash flow after inflation or a discount rate equal to the required annual investment return percentage can be used in order to determine whether the forecasted investment return exceeds or falls short of the required return. Both of these scenarios require the discount rate to be entered as a percentage.
Prime Interest Rates
The PrimeRate sheet includes monthly prime interest rates from January 2000 to December 2050. The interest rates that are entered on this sheet are included in the monthly amortization table on the MonthAmort sheet after deducting the rate discount that is also specified on the MonthAmort sheet.
Note: An up to date copy of all the monthly prime interest rates is available on the Templates page of our website. Users can therefore simply download this free Excel file and update the appropriate templates with the values that are listed in this document.
Annual Capital Growth Rates
The annual capital growth rates that are entered in row 4 on the CashFlow sheet are used to calculate the market value of the property at the end of each annual period that is included on the CashFlow and Results sheets. The template therefore includes the flexibility to accommodate variable annual capital growth rates instead of a single average annual capital growth rate that is applied to the entire investment period.
Ad Hoc Bond Repayments
The template facilitates entering ad hoc bond repayments on the AdHoc sheet and automatically includes the ad hoc bond repayment amounts that are entered on this sheet in the monthly amortization table on the MonthAmort sheet. Ad hoc bond repayments can be entered for any monthly period between January 2000 and December 2050.
Interest Rate Discount
As we've mentioned before, the monthly amortization table is based on the prime interest rates that are entered on the PrimeRate sheet and therefore accommodates variable monthly interest rates. In most instances, the annual bond interest rate differs from the prime interest rate by a fixed interest rate discount or premium percentage. The value of the interest rate discount or premium should be entered in cell F6 on the MonthAmort sheet.
Note: The rate discount or premium should not be entered as a percentage because the template calculations convert the specified value to a percentage automatically. Also, the default value is applied as if an interest rate discount is entered (applicable to most bonds) and if you therefore need to specify an interest rate premium, a negative value needs to be entered in cell F6.
Property Investment Cash Flow Calculations
The cash flow calculations on the CashFlow sheet are calculated from the user input that is included on the other sheets in the template and forms an important part of the property investment return analysis on the Results sheet. In this section of the instructions, we'll provide guidance on the calculation methodology that has been applied in order to calculate the annual cash flow amounts on the CashFlow sheet.
The only user input that is required on the CashFlow sheet is the annual capital growth rates in row 4. An annual capital growth rate needs to be specified for each year that is included in the investment period and is used in order to calculate the property market value at the end of the particular period (included in row 5).
The outstanding bond amount at the end of each period is calculated by using the monthly amortization table on the MonthAmort sheet and included in row 6. This amount is deducted from the property market value at the end of each period (row 5) in order to calculate an Equity 1 balance at the end of the period. The Equity 1 amount reflects the equity value before taking selling costs and capital gains tax into account and is discussed in more detail under the Property Investment Return Analysis section of the instructions.
The detailed annual cash flow report includes the following line items:
Transfer Costs - the transfer costs are included in the first year on the cash flow report and consist of the transfer duty, transfer fees and other transfer costs that are entered in row 12 to 14 on the Input sheet.
Bond Costs - the bond costs are included in the first year on the cash flow report and consist of the bond registration fees, bank initiation fees and other bond costs that are entered in row 15 to 17 on the Input sheet.
Deposit Amount - the deposit amount is included in the first year on the cash flow report and is specified in cell B7 on the Input sheet. The deposit amount needs to be entered as a positive value on the Input sheet and usually represents a cash outflow.
The deposit amount input cell can also be used to incorporate the effect of a bond of more than 100% of the property purchase price into the template calculations. When the bond amount is more than the property purchase price, a negative deposit amount can be recorded in the deposit amount input cell which will result in a positive cash flow in the first year on the cash flow report. The bond amount that is included on the monthly amortization table will also equal the amount in excess of the property purchase price.
If the VAT registration checkbox in cell C9 on the Input sheet is selected, the input VAT amount that is included in the property purchase price will also be displayed as a positive cash inflow in the Deposit Amount line on the cash flow report. The cash inflow occurs when the input VAT is claimed back from the South African Revenue Services and output VAT will only have to be paid when the property is sold. The cash inflow is therefore of a temporary nature but the effect of the inflow is included in the first annual period on the cash flow report.
Note: If a deposit amount is specified in cell B7 and the checkbox in cell C9 is selected, the net effect of the deposit amount and the input VAT amount will be displayed in the Deposit Amount line on the cash flow report.
Initial Renovation Costs - this amount is included in the first period on the cash flow report and represents the estimated cost of renovations that will be completed immediately after purchasing the property. The initial renovation costs are specified in cell B10 on the Input sheet and represent a cash outflow.
Rent Income - the rent income amount in the first year is calculated by multiplying the monthly rent income in cell B19 on the Input sheet by the occupancy rate in cell B20 on the Input sheet. The rent income amounts in subsequent years are calculated by adding the rent increase percentage to the appropriate rent income amount of the previous year. All rent income amounts represent an inflow of cash.
If a rent subsidy amount is specified in cell B22 on the Input sheet, the monthly rent subsidy is added as a cash inflow on the cash flow report based on the number of monthly rent subsidy periods that are specified in cell B23 on the Input sheet.
Property Management Fee - the property management fee amounts on the cash flow report are calculated by multiplying the property management fee percentage in cell B25 on the Input sheet by the monthly rent income amount that is specified in cell B19 on the Input sheet. Note that rent subsidies are excluded from the property management fee calculation.
Rates - the rates amount that is included in the first year on the cash flow report is specified in cell B26 on the Input sheet. The rates amounts for all subsequent years are calculated by adding the annual cost inflation percentage that is specified in cell B31 on the Input sheet to the appropriate amount of the previous year.
Levies - the levies amount that is included in the first year on the cash flow report is specified in cell B27 on the Input sheet. The levies amounts for all subsequent years are calculated by adding the annual cost inflation percentage that is specified in cell B31 on the Input sheet to the appropriate amount of the previous year.
Repairs & Maintenance - the repairs & maintenance amount that is included in the first year on the cash flow report is specified in cell B28 on the Input sheet. The repairs & maintenance amounts for all subsequent years are calculated by adding the annual cost inflation percentage that is specified in cell B31 on the Input sheet to the appropriate amount of the previous year.
Insurance - the insurance amount that is included in the first year on the cash flow report is specified in cell B29 on the Input sheet. The insurance amounts for all subsequent years are calculated by adding the annual cost inflation percentage that is specified in cell B31 on the Input sheet to the appropriate amount of the previous year.
Other Operational Costs - the other operational costs amount that is included in the first year on the cash flow report is specified in cell B30 on the Input sheet. The other operational cost amounts for all subsequent years are calculated by adding the annual cost inflation percentage that is specified in cell B31 on the Input sheet to the appropriate amount of the previous year.
Income Tax - the income tax amounts are calculated by deducting the operational costs (row 16 to 21 on the cash flow report) and the total interest for the period as per column F on the MonthAmort sheet from the rent income for the period and then multiplying the result by the appropriate income tax percentage.
The appropriate income tax percentage is determined based on the enterprise form that is selected in cell B37 on the Input sheet and the income tax rates that have been specified for the appropriate enterprise form on the Variables sheet. If the operational costs and interest exceed the rent income, the calculation results in a taxable loss and a positive income tax value. The positive value represents the amount of income tax that is saved by deducting the taxable loss from other taxable income, thereby reducing the income tax that is payable on other sources of income.
Note: The income tax calculations in this template are based on the assumption that ring fencing is not applied. If ring fencing is applied, the costs (including interest) that can be deducted from the rent income are limited to the rent income - in other words, a taxable loss and therefore a positive cash inflow cannot be created and the income tax amount will therefore be nil. The costs that cannot be deducted in any particular tax assessment year can however be carried forward and deducted from future rent income.
Note: For more information on property income tax calculations, refer to our Property Income Tax Calculation template. The income tax calculation relating to residential properties is covered in a lot more detail in this template.
Bond Repayments - the total bond repayments as per the monthly amortization table on the MonthAmort sheet are included as a cash outflow in row 23 on the cash flow report. The total bond repayments include both the required bond repayments in column D on the MonthAmort sheet and the ad hoc bond repayments in column E on the MonthAmort sheet.
Selling Price - the selling price is only included on the cash flow report in the annual period that the end of the investment period that is specified in cell B42 on the Input sheet falls into and is equal to the market value that is calculated in row 5. The market value is calculated by applying the annual capital growth percentages that are entered in row 4 on the cash flow report to the property purchase price that is specified in cell B6 on the Input sheet.
Selling Costs - the total selling costs consist of agents commission and other selling costs. The agents commission amount is calculated by multiplying the agents commission percentage that is specified in cell B33 on the Input sheet by the selling price that is calculated in row 24 on the cash flow report. Other selling costs are calculated by adjusting the other selling costs amount that is specified in cell B34 on the Input sheet by the annual cost inflation percentage that is specified in cell B31 on the Input sheet.
Capital Gains Tax - the capital gains tax amount is calculated by deducting the property purchase price, transfer costs, initial renovation costs and selling costs from the selling price and multiplying the result by the appropriate effective capital gains tax percentage (calculated in cell B39 on the Input sheet).
The initial renovation costs are deducted in the capital gains tax calculation because these costs are of a capital nature and lead to the improvement of the asset (property). The capital nature of renovation costs also means that these costs are not allowed as an income tax deduction.
The effective capital gains tax percentage is calculated base on the enterprise form that is selected in cell B37 on the Input sheet, the income tax rate that is specified for the appropriate enterprise form on the Variables sheet and the capital gains tax inclusion rates that are also specified on the Variables sheet.
Note: If the property is a primary residence and the checkbox in cell C41 on the Input sheet is therefore selected, the capital gains tax exclusions relating to primary residences are also taken into account when calculating the capital gains tax amount. The primary residence exclusions also form part of the input variable values that are included on the Variables sheet and only apply to individuals.
Note: For more information on capital gains tax calculations, refer to our Capital Gains Tax Calculation template. The CGT calculation is covered in a lot more detail in this template.
Bond Cancellation Fee - the bond cancellation fee is only included on the cash flow report in the annual period that the end of the investment period that is specified in cell B42 on the Input sheet falls into and is entered in cell B35 on the Input sheet. Note that the bond cancellation fee is not increased by the operating cost inflation percentage because the amount is usually specified in the bond agreement at the start of the property investment period.
Total Cash Flow for Period - this is the total value of all the cash flow amounts that are calculated in row 11 to 28 on the cash flow report.
Discount Factor - the discount factor is calculated based on the discount rate that is specified in cell B43 on the Input sheet. The total cash flow for the period is multiplied by the discount factor to calculate the present value of the annual cash flows in row 31.
Present Value of Cash Flow - the present value of the annual cash flows is calculated by multiplying the total cash flow for each period in row 29 by the discount factor in row 30. This calculation forms part of the net present value (NPV) calculation in row 23 on the Results sheet. The sum of all the present values equals the net present value at the end of the property investment period.
Note: The property investment period that is specified in cell B42 on the Input sheet can be any investment period between 1 month and 240 months (20 years). If the last annual period that falls into the investment period does not include a full 12 month period, the cash flow calculations in the last period are adjusted accordingly.
Property Investment Return Analysis
A comprehensive property investment return analysis is included on the Results sheet. All the calculations on this sheet are automated and based on the input values that are entered on the other sheets in this template. No user input is required on the Results sheet.
The following investment return calculations are included on the Results sheet:
Net Operational Cash Flow - Annual
The annual net operational cash flow is the total of all the amounts on the CashFlow sheet from row 11 to row 23. The calculation therefore includes all the operating income, expenses and taxation and reflects the net cash flow surplus or shortfall resulting only from the leasing operations. The cash flow relating to the sale of the property is not included in this calculation. This calculation is an important indicator on whether a property investment is cash flow positive.
Net Operational Cash Flow - Cumulative
The cumulative net operational cash flow is a cumulative total of the annual net operational cash flow in row 6. This calculation indicates the amount of cash that needs to be contributed by the owner in order to meet the cash flow requirements of the property investment. The calculation is also based only on operating cash flow and therefore does not include the cash flow relating to the sale of the property.
Equity 1 Calculation
The equity 1 amounts in row 11 are calculated by deducting the outstanding bond amount at the end of each annual period from the market value of the property at the end of each annual period. The appropriate outstanding bond amounts and market values are calculated in row 6 and row 5 on the CashFlow sheet respectively.
Note: Our equity 1 amount is commonly referred to as the equity that is available in a property investment. We don't agree with this methodology because we believe that the estimated disposal cost should be deducted when determining the equity that is available in a property investment, otherwise the amount of equity that is realised when the property is sold will be less than expected.
Equity Balance Calculation
The equity balance indicates the amount of equity that is available in the property investment after taking the effect of disposal costs into account. Disposal costs consist of VAT (if applicable), selling costs, capital gains tax and bond cancellation fees.
VAT
The VAT calculation in row 12 only contains values if the checkbox in cell C9 on the Input sheet is selected because the property investor is VAT registered. The amounts that are calculated equal the output VAT that needs to be paid to the South African Revenue Services when the property is sold. By including the output VAT amounts in the annual equity calculation, a provision is in effect raised for the VAT amount that will be payable and the provision is therefore also taken into account in calculating the equity that is available in the property investment.
Note: As we've mentioned before, the input VAT that can be claimed back from SARS is calculated based on the property purchase price and included in the Deposit Amount line on the cash flow report. The cash inflow as a result of claiming back the VAT is therefore taken into account at the start of the property investment period.
Selling Costs
The selling costs in row 13 are calculated by multiplying the agents commission that is specified in cell B33 on the Input sheet by the market value in row 9 and adding the other selling costs amount that is entered in cell B34 on the Input sheet (after adjusting the amount by the annual cost inflation that is specified in cell B31 on the Input sheet). By including the selling cost amounts in the annual equity calculation, a provision is in effect raised for the selling costs that will be incurred when the property is sold and the provision is therefore also taken into account in calculating the equity that is available in the property investment.
Capital Gains Tax
An annual provision for capital gains tax is calculated by deducting the property purchase price, transfer costs, initial renovation costs and selling costs from the market value in row 9 and multiplying the result by the appropriate effective capital gains tax percentage (calculated in cell B39 on the Input sheet). By including the capital gains tax amount in the annual equity calculation, a provision is in effect raised for the capital gains tax that will be payable when the property is sold and the provision is therefore also taken into account in calculating the equity that is available in the property investment.
Note: If the primary residence checkbox in cell C41 on the Input sheet is selected and the enterprise form is an individual, the primary residence exclusions on the Variables sheet are also taken into account in the capital gains tax calculation.
Bond Cancellation Fees
The bond cancellation fees are specified in cell B35 on the Input sheet. By including this amount in the annual equity calculation, a provision is also raised for this type of cost and the provision is also deducted in the calculation of the equity that is available in the property investment.
Equity Balance
The equity balance is calculated by deducting all the provisions for disposal costs from the equity 1 balance. The balance that is calculated therefore already takes the disposal costs into account and should therefore be an accurate estimation of the equity that can be realised when the property investment is sold.
Equity Contributed (during the year)
The equity that is contributed during the year is equal to the annual net operational cash flow. If a property investment results in a monthly cash shortfall, the property investor needs to contribute equity on a monthly basis in order to cover this shortfall. These contributions form part of the equity balance at the end of each annual period. Similarly, if the property investment is cash flow positive, the positive cash flow amount is in effect returned to the property investor on a monthly basis and therefore needs to be deducted from the equity balance.
Note: This is an important characteristic of residential property investments which is often ignored by property investors. Many property investors are so satisfied by the amount of equity that is available in their property investments that they don't realise that a significant portion of the equity may have been contributed by them in the form of monthly capital injections and therefore does not form part of the profit that is realised from the investment.
Annual Net Profit (Loss)
The calculation of the annual net profit or loss on a property investment could be quite complex, especially in a comprehensive and accurate investment return forecast template like ours. The calculation consists of a number of components. In principle, the annual profit or loss is determined by deducting the operational costs, interest and taxation from the rental income but you also need to take the increase or decrease in the market value (unrealised profit or loss) and the movement in the disposal cost provisions into account.
Note: A simpler method of calculating the annual net profit or loss is by deducting the equity contributed during the year (or the annual net operational cash flow) from the closing equity balance and then deducting the opening equity balance from this amount. The calculation result is the annual net profit or loss on the property investment.
Note: The movement in the equity balance between two annual periods can be split into two components - the equity contributed during the year and the annual net profit or loss. If you therefore deduct the equity opening balance from the equity closing balance, the calculation result will equal the total of the amounts in row 17 and row 18 in the appropriate column.
Cumulative Profit (Loss)
The cumulative profit or loss is quite simply the sum of all the net profits or losses up to the end of the particular period. The cumulative totals in row 19 are calculated from the annual net profit or loss amounts in row 18.
Note: The cumulative profit or loss totals in row 19 will differ from the equity balances in row 16 because the cumulative equity contributions (the cumulative total of the amounts in row 17) also form part of the equity balances.
Annual Capital Growth
The annual capital growth rates are entered by the user in row 4 on the CashFlow sheet.
Average Annual Capital Growth
The average annual capital growth rates are the average overall capital growth that has been realised on the property investment. This rate is calculated based on the market value at the end of the particular period, the property purchase price and the investment period that has elapsed.
Net Present Value (NPV)
The NPV indicates whether the return on investment exceeds the required return on investment that is specified by the user. A positive NPV indicates that the investment return is higher than the required return on investment and a negative NPV indicates that the investment return is below the required return on investment.
The NPV calculation is based on the annual cash flow totals in row 29 on the CashFlow sheet and the discount rate that is specified in cell B43 on the Input sheet. The discount rate is applied to the annual cash flow totals to calculate a present value for all the annual cash flows (calculated in row 31 on the CashFlow sheet). The NPV is equal to the sum of all the annual cash flow present values.
The NPV calculation in this template can be used to calculate the investment return after inflation by simply specifying the average annual inflation rate as the discount rate. Alternatively, the NPV calculation can also be used to determine whether the investment return exceeds a required return on investment by simply specifying the required annual return on investment as the discount rate.
Note: The annual NPV values are calculated based on the assumption that the property is sold at the end of an annual period. This means that the market value and selling costs are also included in the calculation.
Internal Rate of Return (IRR)
The IRR calculation indicates the average annual cumulative return on investment of the property investment. The calculation is based on the annual cash flow totals in row 29 on the CashFlow sheet. The IRR of a property investment takes the effect of all cash flow items into account and therefore provides an extremely useful indication of the overall profitability of a property investment.
The IRR calculation is frequently used in conjunction with the NPV calculation because the one calculation provides an indication of the annual return on investment on a cumulative based (IRR) while the other calculation provides an indication of whether the investment return exceeds the required investment return (NPV). The IRR calculation result is an annual investment return percentage, while the NPV calculation indicates the amount by which the investment return exceeds or falls short of the required investment return. The IRR can also be described as the annual investment return that results in a NPV of nil.
Note: The annual IRR values are calculated based on the assumption that the property is sold at the end of an annual period. This means that the market value and selling costs are also included in the calculation.
Return on Equity
The IRR calculation provides the best indication of the overall or cumulative investment return of a property, but it is not that useful when you want to calculate the investment return that has been achieved during a specific annual period. The movement in the overall IRR that is calculated does provide an indication of whether the investment return for a specific period exceeds the cumulative investment return up to that point, but it is still not possible to determine what the return during the particular annual period is.
Example: If the IRR at the end of year 3 is say 15.6% and the IRR at the end of year 4 is say 11.2%, the movement in the IRR indicates that the investment return that is achieved during year 4 is less than the cumulative investment return up to the end of year 3. The movement in the IRR does however not provide an indication of the investment return that is achieved during year 4 or even if a loss has resulted during this particular period.
The return on equity ratio is calculated on an annual basis instead of a cumulative basis and therefore does provide an indication of what the investment return is for a particular annual period. The return on equity however does not provide an indication of what the cumulative investment return is and the calculation should therefore be used in conjunction with the IRR calculation.
The return on equity is calculated based on the net profit or loss for the period and the equity balance at the end of the particular period. The net profit or loss for the period is calculated in row 18 and the equity balance is calculated in row 16 on the Results sheet.
Rental Yields
The rental yield and net rental yield calculations in this template are based on the market value, property purchase price and outstanding bond amount.
Market Value
The rental yield on the market value is calculated by dividing the rental income in row 15 on the CashFlow sheet by the market value in row 9 on the Results sheet. The percentage that is calculated indicates what the gross income from leasing a property is in relation to the market value of the property. This ratio can therefore be used to compare gross rentals of a property to industry norms or other property investment opportunities.
Property Purchase Price
The rental yield on the property purchase price is calculated by dividing the rental income in row 15 on the CashFlow sheet by the property purchase price that is specified in cell B6 on the Input sheet. The calculation provides an indication of the initial rental yield that is achieved and the increase in yield during the investment period. Note that the yield should increase because rent income typically escalates during the investment period.
Outstanding Bond Amount
The rental yield on the outstanding bond amount is calculated by dividing the rental income in row 15 on the CashFlow sheet by the outstanding bond amount at the end of the particular period in row 10 on the Results sheet. The calculation can be compared to the annual bond interest rate to determine whether the rental income is sufficient to cover the interest on the bond or to determine the specific point in the investment period where the rental income becomes sufficient to cover the interest on the bond.
Note: The rental income typically escalates during the investment period and the outstanding bond amount is reduced through monthly bond repayments. The rental yield that is calculated on the outstanding bond amount should therefore increase during the investment period and at some point in the investment period, the rental yield should exceed the bond interest rate.
Net Rental Yields
The net rental yields are calculated on a similar basis as the rental yields, but instead of dividing only the rental income by the appropriate market value, property purchase price or outstanding bond amount, the net rental income is divided by these amounts. The net rental income is defined as the rental income after operational expenses and is calculated by deducting the costs in row 16 to row 21 on the CashFlow sheet from the rent income in row 15.
Note: We recommend using the net rental yield instead of the rental yield calculation when comparing property investment opportunities because the net rental yield calculation also takes the operational cost structure of the properties into account. The operational cost structure may influence property investment decisions when there is for instance a significant difference between the levies that are payable on two similar property investment opportunities or a significant difference in the condition of two properties which would result in a higher level of expenditure on repairs & maintenance on one of the properties.
Equity 1 / Market Value
This ratio expresses the equity 1 balance in row 11 as a percentage of the market value in row 9. It is an indication of the equity (before taking disposal costs into account) that is available in the property investment.
Disposal Cost %
This ratio expresses the disposal costs as a percentage of the market value in row 9. The disposal costs are defined as the sum of the VAT, selling costs, capital gains tax and the bond cancellation fees in row 12 to row 15.
Note: As we've mentioned before, we believe that it is more prudent and accurate to calculate an equity balance after taking the provisions for disposal costs into account. We therefore recommend deducting the disposal cost % from the equity 1 / market value ratio to determine the percentage equity that is available in a property investment. The equity 1 / market value ratio is however provided because we believe that this is the industry norm for calculating the equity percentage ratio.
Outstanding Bond %
This ratio is calculated by dividing the outstanding bond amount at the end of the appropriate period in row 10 by the initial bond amount that can be calculated by deducting the deposit amount in cell B7 on the Input sheet from the property purchase price in cell B6 on the Input sheet. The ratio indicates the percentage capital that still needs to be repaid on the bond.
Cumulative Profit / Property Purchase Price
This ratio is calculated by dividing the cumulative profit in row 19 by the property purchase price that is entered by the user in cell B6 on the Input sheet. This calculation has been added to the investment return analysis ratios to provide an indication of the profitability of the property investment in relation to the initial purchase price.
Bond Amortization
The monthly bond amortization table on the MonthAmort sheet is automatically calculated from the bond details that are entered on the Input sheet, the prime interest rates that are entered on the PrimeRate sheet and the ad hoc bond repayments that are entered on the AdHoc sheet.
The only user input that is required is specifying the rate discount in cell F6. This value is deducted from the monthly prime interest rates to calculate the monthly bond interest rates in column J. Note that if an interest rate premium is applicable to the bond, the rate discount needs to be entered as a negative value.
The monthly amortization table is used to calculate the annual bond repayment total, annual interest charges and the outstanding bond balance at the end of each annual period. These calculations are included in the appropriate rows on the cash flow report.
The bond repayments total on the cash flow report is calculated as the total of the required bond repayment in column D and the ad hoc bond repayments in column E on the amortization table and included in the appropriate annual bond repayment total in row 23 on the cash flow report.
The interest charges in column F form part of the income tax calculation that is included in row 22 on the cash flow report. The outstanding bond amount at the end of each annual period is included in row 6 on the cash flow report and row 10 on the Results sheet.
The bond amortization table calculations in this template accommodate variable monthly interest rates and ad hoc bond repayments and this enables us to compile a comprehensive investment return forecast which also includes the effects of variable interest rates and ad hoc bond repayments.
Property Calculation Variables
There are a number of property variables that affect the calculation of an accurate property investment forecast. The variables that have been used in the template calculations have been included on the Variables sheet. Some of these variables are subject to regular changes and can be amended by simply entering the appropriate values on the Variables sheet. All the template calculations are automatically updated after amending the appropriate values.
Note: The input table bracket values can also be amended by simply entering the appropriate value in the Value column and you can add additional brackets by simply inserting a new row anywhere between the first and last row in the input table, entering the appropriate bracket and cost values and copying the formulas in the cells with a light blue cell background from one of the existing rows in the input table.
The transfer duty, transfer fees, bond registration fees, deed office levies, bank initiation fees, other transfer costs and other bond costs are all used in calculating the transfer & bond cost values in cell D12 to cell D17 on the Input sheet. VAT is also added to the transfer & bond costs that are subject to VAT.
The VAT percentage is used in the transfer & bond cost calculation and also to determine the input and output VAT amounts if the checkbox in cell C9 on the Input sheet is selected.
The income tax percentages are used to calculate the income tax amounts on the cash flow report and the capital gains tax percentages and exclusions are used to calculate the capital gains tax amounts on the cash flow report and the Results sheet.
Note: An up to date copy of all the variable values that affect residential property calculations is available on the Templates page of our website. Users can therefore simply download this free Excel file and update the appropriate templates with the values that are included in this document.
