Download PIA User Guide - Somerset Financial Services

PIA User Guide
Property Investment Analysis software
"The essential analytical tool for property investors"
Publisher
Somerset Financial Services Pty Ltd
ABN 25 623 732 311
P.O. Box 615 Cleveland QLD 4163
Phone +61 (07) 3286 4368
Fax
+61 (07) 3821 2005
Website
Technical support
www.somersoft.com.au
support@somersoft.com.au
Please note that the domain name is somersoft, not somerset.
1st Edition
May 2003
nd
2
Edition
Jan 2005
3rd Edition
Aug 2007
July 2008
5th Edition
July 2010
6th Edition
Apr 2013
4th Edition
The PIA User Guide was written by Williams Technical Communication Pty Ltd and Ian Somers.
Copyright
This publication is Copyright © 2005 Somerset Financial Services Pty Ltd. All rights reserved. No part of this publication
may be reproduced, stored in any retrieval system or transmitted, in any form or by any means, electronic, mechanical,
photocopying, recording or otherwise, without the prior written permission of Somerset Financial Services Pty Ltd.
Disclaimer
This publication is designed to provide authoritative education and training in the use of Property Investment Analysis
software. While Somerset Financial Services Pty Ltd has taken all reasonable care in the preparation of this publication,
it may contain typographical errors or technical inaccuracies. Furthermore, as tax laws are constantly changing, the
accuracy of this information cannot be guaranteed. Changes will be made periodically and without notice to both the
software and the information herein and these changes will be incorporated into later editions. Materials and examples
contained herein do not constitute financial advice as there may be many issues outside the realm of this software and
publication that should be considered in making or modifying an investment.
Warranty
Somerset warrants that the medium on which this software is furnished to be free from defects for a period of 90 days
after delivery. Somerset does not warrant that the functions contained in the software will meet your requirements or that
the software operation will be error free. In no event will Somerset be liable to you for any damages, including any lost
profits, lost savings, or other incidental or consequential damages arising out of the use or inability to use this program, or
for any claim by any other party. The entire risk as to the results and performance of the PIA program and documentation
is assumed by you.
Help
Before contacting technical support (support@somersoft.com.au) please review the PIA Quick-Start Guide and this user
guide. Help is also available within PIA via the Help menu and via the ? buttons in dialogs.
The PIA website provides access to
Frequently Asked Questions (FAQs) with answers
Discussion group for property investors
Updates as they are released
Free subscription to the email update notification service
www.somersoft.com.au
Caveat
Dear PIA User,
This edition of the PIA User Guide is based on Version 7.5 Edition 01.
PIA software is subject to change without notice.
Documentation may be changed to reflect changes to PIA software and as the result of user feedback. Your feedback on the
documentation is welcome. Please use the email function at the Somersoft website (www.somersoft.com.au).
Updates to this PIA User Guide and the PIA Quick-Start Guide will be published on the Somersoft website.
Thank you.
.
PIA User Guide
Page ii
PIA Version 7.320
Contents
Contents
Chapter 1
1.1
1.2
1.3
1.4
Chapter 2
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.9
Chapter 3
3.1
3.2
3.3
3.4
3.5
3.6
3.7
3.8
3.9
3.10
3.11
3.12
3.13
3.14
3.15
3.16
3.17
3.18
3.19
3.20
3.21
3.22
Chapter 4
4.1
4.2
4.3
4.4
4.5
4.6
4.7
4.8
4.9
4.10
4.11
4.12
4.13
4.14
4.15
4.16
4.17
4.18
4.19
Chapter 5
5.1
5.2
5.3
5.4
5.5
Welcome to PIA
Overview of PIA structure
Tips on finding information
Conventions
Versions
User Interface
Congratulations screen
Welcome screen
Specify Country Defaults dialog
Data Entry Check List
Elements of the PIA window
Elements of the PIA spreadsheets
Dialogs
Preferences
Windows and sizing
Investment Analysis spreadsheet
Year row
Property value row
Purchase costs row
Investments row
Loan amount row
Equity row
Capital growth rate row
Inflation rate (CPI) row
Gross rent/week row
Interest row
Rental expenses row
Pre-tax cash flow row
Depreciation of building row
Depreciation of fittings (or chattels) row
Loan costs row
Total deductions row
Tax credit row
After-tax cash flow row
Rate of return (IRR) row
Pre-tax equivalent row
Your cost/(income) per week
Property Details area
Home Loan Analysis spreadsheet
Year row
Principal residence row
Capital growth rate row
Equity row
Amount owing row
Refinance costs row
Regular Repayments row
Extra repayments row
Irregular repayments row
Total repayments row
Regular redraws row
Irregular redraws row
Total redraws row
Interest rates row
Interest row
Loan balance row
Loan value ratio row
Term remaining row
Your net cost per year
Wealth Builder spreadsheet
Start of year row
Home value row
Home loan balance row
Investment property value row
Total inv. properties row
PIA User Guide
1
2
3
3
4
5
6
7
8
9
10
12
14
15
16
17
18
19
21
23
25
28
29
30
31
33
35
37
38
39
41
42
43
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
59
60
61
61
61
62
62
63
64
64
65
67
67
68
68
68
Page iii
PIA Version 7.320
Contents
5.6
Total of all loans row (start of year)
5.7
Total of all values row (start of year)
5.8
Loan value ratio row
5.9
Rental income row
5.10
Other income row
5.11
Total income row
5.12
Rental expenses row
5.13
Living expenses row
5.14
New tax row
5.15
Loan payments row
5.16
Total expenditure row
5.17
Cash savings row
5.18
Debt service ratio row
5.19
End of year rows
5.20
Total value row (end of year)
5.21
Total loans row (end of year)
5.22
Net worth row
5.23
Investment equity row
Chapter 6
Linked Loans spreadsheet
6.1
Structure of the Linked Loans spreadsheet
6.2
Using the Linked Loans spreadsheet
6.3
Investment property rows
6.4
Investment term and surplus
6.5
Home value rows
6.6
Loan interest row
6.7
Loan balance row
6.8
Total LVR (loan value ratio) row
6.9
Term remaining
6.10
Using the Capitalise interest and Capitalise expenses options
6.11
Your total net cost per year
Chapter 7
Menus and toolbars
7.1
File menu
7.2
Edit menu
7.3
Report menu
7.4
Graphics menu
7.5
Investor menu
7.6
Calculator menu
7.7
View menu
7.8
Settings menu
7.9
Help menu
Appendix A – Glossary
Appendix B – Table of figures and dialogs
Appendix C – Abbreviations
Index
142
PIA User Guide
Page iv
69
70
71
72
73
74
74
75
77
78
78
79
80
81
81
81
82
83
84
86
87
88
89
89
90
91
92
93
93
93
94
95
99
100
102
105
113
121
122
132
135
140
141
PIA Version 7.320
Chapter 1
Welcome to PIA
Welcome to PIA Property Investment Analysis, a powerful program designed to make it easy for investors to analyse and
evaluate prospective property investments.
PIA helps you analyse the capital growth, cash flows and rates of return on investment properties, taking tax implications
into account. It is simple to use and – as a decision tool – provides the answers to a long list of "What ifs?" on property
investment. PIA is an analysis tool – it is not an accounting tool.
On the market since 1989, PIA has become an industry standard in Australia and New Zealand, used extensively by
leading real-estate groups, property marketers, banks, lenders and accountants, and by thousands of property investors.
PIA was developed to complement the Building Wealth property investment bestsellers by Jan Somers, the latest in the
series being More Wealth from Residential Property.
What PIA can do for you
Briefly, PIA can help you make objective decisions about a potential property investment. You can quickly estimate your
rate of return, tax savings, true after-tax cost, and affordability. Furthermore, you can very easily test the sensitivity of
your results (i.e. playing “What if?”) by making changes to the variables like growth, inflation and interest rates. Finally
you can print out a professionally structured report to help you present your investment proposal to your accountant or
bank manager.
Disclaimer
As the name suggests, PIA is investment analysis software. It is not accounting software. It is intended for analysing and
evaluating prospective property investments, not for generating data for tax returns.
Also, tax law varies from country to country and is constantly changing. For this reason, information given relating to tax
law should be regarded as guidance only. Where possible, this User Guide indicates sources of further information.
Getting started...
This PIA User Guide assumes that you already have the PIA software up and running and that you are acquainted with
the contents of the PIA Quick-Start Guide and that you have worked through the example given there.
The PIA Quick-Start Guide helps first-time users get started with PIA, guiding you through installing your software,
starting it, and entering your first set of data.
Structure of the book
This User Guide is a reference work. The structure is based on the software itself:
Chapter 1
Welcome to PIA
Introduction to PIA and conventions used.
Chapter 2
User Interface
Description of features and how to use them.
Chapter 3
Investment Analysis spreadsheet
Chapter 4
Home Loan Analysis spreadsheet
Systematic description of each
Chapter 5
Wealth Builder spreadsheet
spreadsheet
Chapter 6
Linked Loans spreadsheet
Chapter 7
Menus and toolbars
Systematic description of menus and toolbars
Appendix A
Glossary
Definitions of investment and PIA terms
Appendix B
List of dialogs
Quick access to dialogs and their descriptions.
Appendix C
Abbreviations
Abbreviations used in PIA interface and filenames.
Index
Find text based on keywords.
As a principle, information is documented once only and cross-referenced. For example, the Loan Costs dialog can be
accessed in 3 different ways from the Investment Analysis spreadsheet, but is only documented fully in one place (see
3.15).
Getting best results in Acrobat Reader
A limitation of Acrobat means that screenshots from Microsoft WORD are not rendered with 100% accuracy. Best results
are obtained with the magnification set to 134% or 192%. This Acrobat file has been set to a default magnification of
134%.
PIA User Guide
Page 1
PIA Version 7.320
Chapter 1
Welcome to PIA
1.1
Overview of PIA structure
Spreadsheets: PIA comprises four separate spreadsheets (Investment Analysis, Home Loan Analysis, Wealth Builder
and Linked Loans) for providing instant feedback for a wide range of what-if analyses. A single PIA file contains the data
for all four spreadsheets.
Dialogs: There are over 100 different dialogs supporting these spreadsheets. Their purpose ranges from data entry to
explaining in detail the derivation of spreadsheet variables. Many dialogs provide spreadsheet-like functionality in their
own right (e.g. Investment Capacity and Borrowing Capacity dialogs).
Reports: The desired end product of analysis is often documentation of the results. PIA provides a wide variety of
standard and customised reports for this purpose. These reports can be printed, saved independently (as Adobe Acrobat
PDF files) of the PIA file, or even sent as email attachments (also in Adobe Acrobat PDF format). Acrobat PDF files
require the free Adobe Acrobat reader (available from www.adobe.com) to be viewed.
Investment Analysis spreadsheet (Chapter 3)
The Investment Analysis spreadsheet allows you to evaluate any investment property. As you know from the PIA QuickStart Guide, you can go straight to an Investment Analysis by entering data via the Data Entry Check List.
Home Loan Analysis spreadsheet (Chapter 4)
The Home Loan Analysis spreadsheet is independent of the Investment Analysis spreadsheet and allows you to explore:
•
•
Accelerated mortgage reduction (such as additional payments, fortnightly payments, or using a credit line)
Loan consolidation and refinancing
You must make entries into these two spreadsheets before you can use the more advanced features of PIA.
Investment
property
Current
portfolio
Ow n
home
Wealth Builder spreadsheet (Chapter 5)
This provides an interface that integrates data on from the Investment
Analysis spreadsheet (the investment property under review), the
Home Loan Analysis spreadsheet (your home mortgage) and any
existing property portfolio (Portfolio Analysis).
The spreadsheet allows you to interactively build a portfolio of
properties based on projected multiples of the investment property
under review. At each step (that is, each year) you can see whether
you would have the necessary funds (cash savings) and whether the
banks would be likely to lend you the finance (loan value ratio and
debt /service ratio).
Wealth
Builder
Investment
loan
Home
loan
Linked
loan
PIA User Guide
Linked loans spreadsheet (Chapter 6)
This spreadsheet provides an interface for exploring methods of
accelerated mortgage reduction where there are both investment
and home loans and any cash flow surplus from the investment can
be used to help repay the home loan.
If a credit line has been used as a means of reducing a home loan,
the Linked loans model allows account to be taken of servicing
commitments for any investment loan.
Page 2
PIA Version 7.320
Chapter 1
Welcome to PIA
1.2
Tips on finding information
Use the Contents at the front of the book to guide you to broader areas of interest (such as spreadsheets,
menus or scenarios).
If you need information on a specific row, dialog or field, check the index at the back of the book.
See the glossary in Appendix B for definitions of terms.
Defined terms are hyperlinked in the document:
Internal Rate of Return is an example of a defined term.
If you get stuck/
First, please read through PIA Quick Start Guide and this PIA User Guide.
Secondly, use the online help. You can access this from anywhere in the main part of the PIA program by pressing F1, or
by accessing Help via the Help menu.
Tool tips are available for the icons in the toolbar of the spreadsheet and for the buttons in the Data Entry Check List.
These appear if you position the mouse pointer over the icon or button. You can get context-sensitive help by clicking on
the ? button in most dialogs.
If all else fails, you can obtain technical support by sending an email to support@somersoft.com.au or calling the help
line (07) 3286 4368. The Help line will help you – and you will help us by providing feedback.
1.3
Conventions
This is an example of an Information paragraph. These paragraphs contain additional “could know” information.
&
This is an example of a book reference. It indicates a source of further information.
This is an example of a defined term. Defined terms are hyperlinked – clicking on them displays the glossary definition.
(See 3.5) is an example of a cross-reference, in this case to Chapter 3, section 5. In order to reduce the size of the PIA
User Guide, detailed descriptions appear once only and are cross-referenced in this way.
User feedback
We welcome feedback from users both on the documentation and the software itself – it helps us to give you what you
want. So please do not hesitate to make comments or suggestions. You can use the email function at the website to
submit your comments.
Navigation tips
You can use the table of contents (on pages ii and iii at the front of this document) to navigate.
Use the Table of Dialogs at the end of the manual to find a particular dialog.
PIA User Guide
Page 3
PIA Version 7.320
Chapter 1
Welcome to PIA
1.4
Versions
The PIA User Guide documents the fully featured versions of the PIA, including spreadsheets for home loan analysis,
linked loans analysis, and a wealth builder for interactively building a property portfolio over a number of years. PIA’s
extensive graphics, suite of property and finance calculators, and the wide array of report options are also documented.
This PIA User Guide is based on PIA Version 7.2 using the Windows XP operating system.
If you are using an earlier or later version of Windows (such as Windows 98, Windows 2000, Vista, Windows 7 or 8),
windows and dialogs may have a slightly different appearance, as shown below.
Dialogs with Windows XP
Dialogs with earlier version of Windows
PIA User Guide
Page 4
PIA Version 7.320
Chapter 2
User Interface
This chapter describes the user interface of PIA. The chapter covers the following information:
Congratulations screen
Welcome screen
Specify Country Defaults dialog
Data Entry Check List
Elements of the PIA window
Elements of the PIA spreadsheets
Dialogs
Preferences
Windows and sizing
PIA User Guide
Page 5
PIA Version 7.320
Chapter 2
The User Interface
2.1
Congratulations screen
The first time you ever use PIA on a particular computer, you will be prompted to enter your registration number (see
your receipt or the back of your CD case). A registration number is not requested if you are upgrading on the same
computer.
When you enter the correct number, the confirmation message "Welcome to the World of Property Investment
Analysis" is displayed.
If you enter the wrong registration code, a warning message appears and PIA runs in demonstration mode:
For further details of getting started with the software, please see the PIA Quick-Start Guide.
PIA User Guide
Page 6
PIA Version 7.320
Chapter 2
The User Interface
2.2
Welcome screen
When the PIA Welcome screen appears, you have the choice of opening the latest property file you saved, opening
any previously saved file, or creating a new property file. If you are using PIA for the first time, select the Create a new
property file radio button and click on OK.
Open your latest property file
This open opens the last property file you saved.
Open an existing property file
This option opens a window displaying your property files. Unless you have selected a different directory for saving
your files, they are saved in the default location of the PIA program directory. See 7.1 for details of how to change the
default directory.
Create a new property file
This option creates a new file using the default settings you have chosen. You can set these in the Data Entry Check
List (see 2.4).
The Welcome screen also tells you which edition and version of the software you are using.
In this example, it is the Personal Investor Version 7.501.
The first two digits (7.5) represent the actual version number while the last two (01) represent the edition number.
Updates of the software are released from time to time and are available from the website: www.somersoft.com.au
PIA User Guide
Page 7
PIA Version 7.320
Chapter 2
The User Interface
2.3
Specify Country Defaults dialog
When you use PIA for the first time, the Specify Country Defaults dialog is displayed.
This dialog allows you to select between Australia and New Zealand defaults for a number of areas where the two
countries differ. These include:
Tax scales
Stamp duties (none in NZ)
Sales commission
Financial year dates
Capital allowance rules
Depreciation rates
Terminology, for example fittings (Australia) vs chattels
(NZ)
Loan cost items
Loan cost write-off period
Capital gains tax (none in NZ)
You can also vary individual defaults within PIA.
You can access the Specify Country Defaults dialog at any time by selecting Specify Country Defaults from the
Settings menu.
&
PIA automatically displays appropriate dialogs if you change the country default:
If you change the default to New Zealand, the Data Entry Check List (described below) is displayed.
If you change to the default to Australia, the Specify Stamp Duty Scale dialog is displayed (allowing you to select the
appropriate stamp duty) and then the Data Entry Check List (described below).
For details of tax regulations specific to Australia and New Zealand, please refer to the following documents:
Australia
Rental Properties (NAT 1729), Australia Taxation Office, www.ato.gov.au
New Zealand
Rental Income (IR 264), Inland Revenue, www.ird.govt.nz
PIA User Guide
Page 8
PIA Version 7.320
Chapter 2
The User Interface
2.4
Data Entry Check List
When you select Create a new property file from the Welcome screen and click OK, the Data Entry Check List is
displayed. This dialog is a quick, easy and intuitive way for new users to enter all the basic data for a new property
file.
The Data Entry Check List is a useful but not a critical step in analysing a property investment. As you will see later,
all of the variables in the Check List can be entered directly from the main spreadsheet. This has the advantage of
showing instant feedback, but is less intuitive and does not show which variables have yet to be changed.
The figure above shows the Data Entry Check List Property page with its original default values.
The Data Entry Check List is divided into four tabbed pages of variables covering the key components of a property
analysis:
Property
Finance
Investor
What If?
Details of the property
How the property is financed
Personal and tax-related details
Allows you to see what happens over time with different inflation and growth rates
You can access a page by clicking on the corresponding tab.
Using the Data Entry Check List
The Quick-Start Guide contains a detailed description of how to use the Data Entry Check List.
Skipping the Data Entry Check List
If you wish to skip the Data Entry Check List, you can either simply click on OK when it is displayed, or you can
bypass it entirely by selecting Preferences from the Settings menu (see 7.7). Click on the General tab and check the
Skip check list on new property file check box.
Returning to the Data Entry Check List
If you wish to return to the Data Entry Check List at any time, select Data Entry Check List from the View menu
or click on the Data Entry Check List icon in the toolbar.
Your current settings are shown – as well as the ticks indicating which values you reviewed and or changed.
Changes you make directly in the spreadsheet are reflected in the Data Entry Check List values, but tick marks only
appear for the items you accessed via the Check List.
Setting defaults
You can make the current values shown in the Data Entry Check List the default values for PIA by checking the Use
these values as the defaults check box. New files will then open with these values set. The values will only be
adopted if you click OK to exit the Check List.
You can reset PIA’s “factory defaults” by selecting Delete Default Template from the Settings menu. Exit and restart
PIA to make sure that all changes take effect.
For a full description of using the Data Entry Check List, please see the Quick-Start Guide, page 12 ff.
PIA User Guide
Page 9
PIA Version 7.320
Chapter 2
The User Interface
2.5
Elements of the PIA window
Title bar
Menu bar
Spreadsheet
Toolbar
License header
Property details
The figure above shows the PIA interface with the Investment Analysis spreadsheet open. This is the default
spreadsheet and the one in which you will spend most time.
Title bar
The title bar shows the name of the file and the name of the software: Somerset PIA.
On the right of the title bar are the standard Windows boxes for minimizing, maximizing and closing the window.
Menu bar
The menu bar shows the names of the PIA menus. Clicking on a menu name opens a drop-down menu.
The menus and their items are described in detail in Chapter 7.
Toolbar
Beneath the Menu bar is the Toolbar consisting of a series of icons, each of which provides quick access to a
commonly used function (see also Chapter 7). If you position the mouse pointer over an icon, a "tool tip" appears,
telling you the meaning of the icon. For example, you can click the Check List icon (shown on left) if you wish to return
to the Data Entry Check List.
New, Open, Save
Print
Undo / redo
PIA User Guide
Spreadsheets
Data Entry Check List
Page 10
Settings icons
Scroll Buttons
About
Help
PIA Version 7.320
Chapter 2
The User Interface
Data Entry Check List
Clicking this symbol takes you back to the Data Entry Check List (see 2.4). New users of PIA, in particular, find this an
easy way of entering data for a new property.
Undo/redo icons
The Undo and Redo icons allow you to undo changes (up to the last 5 changes made).
This is useful when changing values to examine What if? scenarios with the software.
The icon is dimmed if the option (undo or redo) is not available.
Spreadsheet icons
Clicking on these icons take you to the various PIA spreadsheets:
Takes you to the Investment Analysis spreadsheet (see Chapter 3).
Takes you to the Home Loan Analysis spreadsheet (see Chapter 4).
Takes you to the Linked loans spreadsheet (see Chapter 6).
Takes you to the Wealth Builder spreadsheet (see Chapter 5).
Settings icons
The settings icons allow you to make settings to determine how loans are calculated:
Links loans (Investment Analysis and Home Loan Analysis) (see Chapter 6).
Sets “Credit line” (see Chapter 3, 4).
(The Settings menu is described in detail in 7.7.)
Scroll icons
The scroll icons in the Toolbar allow you to scroll through the years. While PIA calculates
projections up to 40 years, only 5 years are shown at any time. By default, these are the years
1,2,3,5 and 10.
Status bar
If displayed, the status bar appears at the very bottom of the window:
You can switch display on and off in the View menu.
The left area of the status bar describes actions of menu items as you use the arrow keys to navigate through menus.
It also shows tips that describe the actions of toolbar buttons when you click on them (before releasing them). If after
viewing the description of the toolbar button you choose not to execute the command, move the mouse pointer off the
button before releasing the mouse button.
The right area of the status bar indicates latched keys:
CAP
Caps Lock is latched on.
NUM
Num Lock is latched on.
SCRL
Scroll Lock is latched on.
The example below shows the status bar tip with the Home Loan Analysis button in the toolbar pressed and the
CAPS, NUM and SCRL locks latched on.
PIA User Guide
Page 11
PIA Version 7.320
Chapter 2
The User Interface
2.6
Elements of the PIA spreadsheets
License header
Projections title
Titles column
Projections
column headers
Projections columns
The bottom line
Input column
The figure above shows the PIA interface with the Investment Analysis spreadsheet open. This is the default
spreadsheet and the one in which you will spend most time.
This spreadsheet shows the real power of the PIA software: You can make changes to variables in the spreadsheet
and immediately see the projected impact over a period of up to 40 years.
What can you change?
PIA uses different colours to indicate which variables you can change and how you can change them.
Blue values are editable. You can click on them and overwrite them directly. Double-clicking on them opens
the accompanying dialog.
Black or red values are calculated values. Clicking on these opens an accompanying dialog, as these
values are calculated by PIA and cannot be overwritten directly. Negative calculated values are shown in red.
Although new users are encouraged to use the Data Entry Check List at first, once you gain experience, you can
bypass this feature and enter data directly into the spreadsheet.
Titles column
The Titles column contains the name of the spreadsheet (here: Investment Analysis) and the row titles.
Where appropriate, clicking on the row title opens a corresponding dialog, which then allows you to drill down to
various entries and settings that result in the values shown. These row titles change colour when the mouse pointer
passes over them and the mouse pointer turns to the familiar pointing hand. The row titles are dynamic, changing to
reflect the current input settings.
Input column
The Input column contains the variables from which the annual projections are calculated. Collectively, these
variables define the attributes of the investment property, how it is being financed, and the rates at which some
variables change.
Projections columns
This area of the spreadsheet contains the annual projections based on the input variables specified in the input
column. While PIA calculates projections up to 40 years, only five years can be displayed at a time. These five years
do not need to be consecutive years (for example, years 1,2,3,5,10), but the year in the right-most column
determines the time frame over which the Internal Rate of Return is calculated.
PIA User Guide
Page 12
PIA Version 7.320
Chapter 2
The User Interface
The scroll icons in the Toolbar allow you to scroll through the years. While PIA calculates projections up to 40 years,
only 5 years are shown at any time. By default, these are the years 1,2,3,5 and 10.
The "bottom line”
The bottom rows of the spreadsheet represent your “bottom line”. The figures for values such as Rate of return and
Pre-tax equivalent are based on the period displayed in the projections columns.
License header
At the top of the spreadsheet area is the license header. This shows the licensee and the type of license.
The examples shown in this User Guide show John Dough – for personal use only, indicating that the software is a
single- user version licensed to John Dough.
Column headers
The column headers identify the input column and the projections columns. The title above the projections columns
indicates the span of the projection (here: Projections over 10 years).
For full details of the spreadsheets, please see the appropriate chapter:
Chapter 3
Investment Analysis spreadsheet
Chapter 4
Chapter 5
Chapter 6
PIA User Guide
Home Loan Analysis spreadsheet
Wealth Builder spreadsheet
Linked Loans spreadsheet
Page 13
PIA Version 7.320
Chapter 2
The User Interface
2.7
Dialogs
Dialogs allow you to view – and in some cases edit – the variables that make up or determine an item’s value.
Information-only dialogs
Some dialogs only display information. They do not
allow entries to be made. Their purpose is to show
the derivation of a value.
The Internal Rate of Return is an example of such a
dialog.
Interactive dialogs
The majority of dialogs are interactive – they allow you to make entries, thus influencing the calculation of a variable.
The Purchase Costs dialog is an example of an interactive dialog.
It contains fields of various types:
Information field (such as the value of Purchase price)
This is a non-editable field here, as the value is entered in the Property Value dialog (see 3.2), in the Investment
Analysis spreadsheet input column (see Chapter 3), or in the Data Entry Check List (see 2.4).
Data-entry field (such as the value of
Stamp duty)
Text-entry fields (such as Other costs,
which can be overwritten).
Radio buttons (such as Capital cost and
Revenue cost – when one is selected, the
other is deselected)
Tick box (such as Uses Qld, indicating
that the Queensland Stamp Duty Scales
are in use).
Button such as Reset Scales which
access the Specify Stamp Duty Scale,
allowing you to select the appropriate
scale).
The ? button gives access to contextsensitive help.
PIA User Guide
Page 14
PIA Version 7.320
Chapter 2
The User Interface
2.8
Preferences
The Preferences menu item in the Settings menu allows you to tailor the user interface to your personal needs and
preferences.
Here are some of the things you can do:
Change font styles and sizes
The spreadsheets have been designed to display correctly using Times Roman up to font size 11.
Increase magnification
If you have trouble reading the screen, you can increase magnification
Specify file locations
You can tell PIA where to store program files and where to store your PIA data files.
Choose the colours used in graphics
Preferences are covered in detail in the Settings menu (see 7.7).
An example of Preferences: The Colours tab in the Preferences menu item (Settings menu) allows you to choose the
colours used in graphics and for the upper and lower parts of the Linked loans and Wealth Builder spreadsheets (see
7.7).
PIA User Guide
Page 15
PIA Version 7.320
Chapter 2
The User Interface
2.9
Windows and sizing
Here is a quick summary of the user interface features of Microsoft Windows that affect PIA.
The figure below shows a “downsized” PIA window with various elements.
Maximize
PIA symbol
Minimize
Filename and application name
Close
Title bar
Border
The title bar of the Windows window shows the PIA symbol, the filename and application name (Somerset PIA) and
the Minimize, Maximize and Close symbols.
Resizing a window
Minimize
Maximize
Restore
Click the Minimize symbol to minimize the window to a taskbar
button. Click the taskbar button to restore the minimized
window.
Click the maximize symbol to make the window fill the whole screen.
Click on the symbol in the maximized window (which replaces
the Maximized symbol when window is already maximized) to
restore the window to the previous size.
You can manually resize an open window in the normal way:
Width
Height
Height & width
Point to the left or right window border – the pointer changes into a horizontal
double-headed arrow. Drag the border to desired position.
Point to the top or bottom window border – the pointer changes into a vertical
double-headed arrow. Drag the border to desired position.
Point to any window corner - the pointer changes to a diagonal double-headed
arrow. Drag the corner to the desired position.
For full details, refer to Microsoft Windows online help.
PIA User Guide
Page 16
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
This chapter provides you with a guided tour of the Investment Analysis spreadsheet and its related dialogs. When you
have completed the data entry check list for a particular property (see 2.4) you will spend most of your time here in this
spreadsheet
This spreadsheet allows you to analyse and evaluate prospective property investments. It does this by calculating the
after-tax cash flows for the investment and, from those cash flows, calculating the Internal Rate of Return. The
spreadsheet lists the key input variables that describe the property under review, the way in which it is financed, the
investor’s current taxable income and rate variables on which forward projections are based. It shows both current data
and the projections based on that data. This spreadsheet thus allows you to quickly examine a range of "What if?"
scenarios simply by modifying the input variables.
Where you have not made any changes, projections are based on default values.
The chapter covers each row (and the associated dialogs and info boxes) from top to bottom. For your orientation, the
row being described is shown with the row above and below:
Property value
Purchase costs
Investments
PIA User Guide
$400,000
$14,425
$40,000
424,000
449,440
Page 17
476,406
535,290
716,339
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.1
Year row
John Doe – for personal use only
Investment Analysis
End of year
Property value
Input
2013
$400,000
1yr
424,000
Projections over 10 years
2yr
3yr
5yr
449,440 476,406 535,290
10yr
716,339
The value in the input column on the spreadsheet shows the year of purchase.
The values in the projection columns display the projected value of the property in selected years from the time of
purchase. The maximum value is 40 years. A value of zero displays no information displayed in that column. The years
do not need to be consecutive or even in chronological sequence, but the year shown in the right-most column
determines the range of years over which PIA calculates the Internal Rate of Return (see 3.19).
Set Projections dialog
Clicking on the Year row title opens the Set Projections dialog.
In the example, the Investment Analysis spreadsheet shows a projection over
10 years, so the Internal Rate of Return shown near the bottom of the input
column is the Internal Rate of Return calculated over 10 years.
To change the years displayed do any one of the following:
Double-click on the Year row title and select from the Set projection
dialog.
Overwrite the year value in each cell of the row. (The values are
blue, indicating that they are editable.)
Use the toolbar scroll s (“>”, “>>”,”<”, “<<”) to select the next, next
five, previous, or previous five years.
Using the Right-Arrow key “->” when the right-most year is selected
also scrolls to the next year.
Note that PIA always calculates 40 years of projected values irrespective of
the year shown in the right-hand column. These variables determine what is displayed in the spreadsheet columns, not
what is calculated.
Notional or actual years
You can set PIA to display notional or actual
years.
Select Preferences from the Settings menu.
The Preferences dialog is displayed. You
can select Notional years or Actual years by
clicking on the appropriate radio button .
Radio buttons for Notional
or actual years
PIA User Guide
Page 18
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.2
Property value row
End of year
Property value
Purchase costs
2013
$400,000
$14,425
1yr
424,000
2yr
449,440
3yr
476,406
5yr
535,290
10yr
716,339
The value in the input column of the Property value row on the spreadsheet shows the property value at the time of
purchase. Normally this is simply the price paid for the property, although this does not have to be the case (see below).
If the initial property value shown in the input column is overwritten, PIA assumes that the value and the price paid are
the same.
The values in the projection columns show the anticipated increase in the property value over the projection period. PIA
calculates these using the specified sequence of capital growth rates. However, the property value in any year can be
selected and overwritten, in which case the corresponding Capital growth rate parameter is recalculated.
Property Value dialog
Clicking on the Property value row title or double-clicking on a cell containing one of the annual values opens the
Property Value dialog. This allows you to make adjustments for renovations or the additional purchase of a furniture
package. It also allows you to enter a different market value if is the not the same as the purchase price of the property
(book value). The initial year is indicated in an editable field (example: 2005). As all cash flow projections are collated in
notional years from this point in time, it is denoted as "End of year".
Property price
In most cases, the price paid
for the property defines the
property’s value and
determines the amount of
State government stamp duty
payable. Clicking on this
button will provide options for
the property being a house
and land package whereby the
stamp duty can be calculated
on the land value only.
Renovations
If renovations are carried out
immediately after purchase,
this impacts upon the property
value and its potential for
capital growth, but does not
affect any stamp duty paid on
the original purchase price.
You may enter renovation
costs in the initial column (at
time of purchase) or in
subsequent years.
Book value
The book value is the sum of the purchase price plus the cost of any renovations undertaken at the time of purchase.
Market value
If you consider that the true market value of the property differs from the book value, you can specify the market value
independently. The values shown to the right of the initial market value reflect the calculated capital growth together with
the value of any renovations undertaken in subsequent years.
The years shown in the Property Value dialog depend on the years currently displayed in the Investment Analysis
spreadsheet as defined in the Set projections dialog described in 3.1.
Holding costs
These are the interest costs associated with the construction phase of the property before the property is available for
rent. As the PIA cash flows only begin from settlement, holding costs may need to be specified and are assumed to
occur at settlement. Clicking on this button provides a template by which the holding costs can be calculated.
Furniture package
A furniture package is often purchased as an optional addition for a resort or holiday unit so that it can be rented fully
furnished. Furniture is a depreciable item and the cost is taken into account when calculating the total loan required.
PIA User Guide
Page 19
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
However, for the purposes of the investment analysis, furniture is not considered as part of the property value subject to
capital growth.
The depreciated values of a furniture package are shown in the columns to the right of the initial value. Stamp duty is not
payable on the cost of furniture.
Property Details
These fields allow you to enter the address of the property and a brief description. This information is displayed in the
various reports you can generate (see Report menu in Chapter 7).
Linked values
To make it quick and easy you to analyse potential property investments, three input variables are, by default, linked to
changes in the property value:
Value of fittings
By default, if the property price changes, the total value of fittings is reset to a set percentage (6%) of the
property price and individual items are consolidated into two categories (General fittings and Low-value pool). If
you choose to sever this link, they must be set manually in the Depreciation of Fittings dialog described in 3.14.
Construction cost
By default, if the property price changes, the original construction cost of the building is reset to 50% of the
property price and building depreciation calculated accordingly. If you choose to sever this link, it must be set
manually in the Depreciation of Building dialog described in 3.13.
Stamp duty
By default, if the property price changes, the stamp duty is recalculated from the currently selected stamp duty
scale. See Specify Stamp Duty Scale dialog in 3.3.
Use the toolbar scroll buttons (“>”, “>>”,”<”, “<<”) to select the next, next five, previous, or previous five years.
Annual Growth Rates button
Clicking on the Annual Growth Rates button displays the Investment Property Growth dialog (shown below).
To simulate an investment property that is to be built, you can either specify the land cost as the property price (attracting
stamp duty) and the building cost as a renovation cost. Alternatively you can specify the cost of land and building as the
property price, but calculate the stamp duty for the land only using the Stamp Duty Calculator in the Calculator menu,
described in 7.5. You can then overwrite the stamp duty calculated (see Purchase cost dialog described in 3.3) and
sever the link between stamp duty and property value.
PIA User Guide
Page 20
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.3
Purchase costs row
Property value
Purchase costs
Investments
$400,000
$14,425
$40,000
424,000
449,440
476,406
535,290
716,339
The value in the input column on the Purchase costs row shows the total purchase costs. This value cannot be
overwritten directly because it is made up of several components such as solicitor’s fees and Government stamp duties.
Purchase Costs dialog
Clicking on the Purchase costs row title, on the value
in the input column or on the projection columns
opens the Purchase Costs dialog.
Purchase price
The purchase price is the amount paid to purchase the
property and the amount on which the stamp duty is
calculated. It is a non-editable field in the Purchase
Costs dialog, as the value is entered elsewhere (either
in the Property Value dialog (see 3.2), the Investment
Analysis spreadsheet, or in the Data Entry Check List
(see 2.4).
Conveyancing costs
Conveyancing costs can vary substantially, depending
on who you have do the work, how much legal work is
involved, and the cost of the property. Some items for
which your solicitor charges (for example, search
fees), may be better recorded under Loan costs if they
are conditional on the provision of finance. This is
because loan costs normally confer tax deductibility
sooner than purchase costs. The Loan Costs dialog is
reviewed in 3.15.
Other costs
There is the provision for recording both the type and amount of any other purchase costs.
Tax status
In most cases, purchase costs are treated as a capital cost (that is, for tax purposes, they are only considered at the time
of sale when calculating any capital gains tax liability). However in places like the A.C.T. where the land is long-term
leasehold, they are considered a revenue cost and are deductible in the year of purchase. You need to check this with
your accountant.
Stamp duty
In Australia, stamp duty is tax levied by state governments on real-estate transfers. By default, PIA calculates stamp duty
as a function of the purchase price and the selected stamp duty scale. However, there are circumstances (for example,
when you are building after purchasing land and building an investment property) where you may wish to calculate it
separately and overwrite the value. To do this, select the Stamp Duty Calculator from the Calculator menu (see 7.5).
Stamp Duty Scales
In Australia, stamp duty is a state-levied tax on the transfer of real property. If you tick the check box for your state, PIA
recalculates the stamp duty from the specified scale for the state.
Reset scales
The stamp duty scales vary according to the state or territory, and can be set in the Purchase Costs dialog by clicking on
the Reset Scales button. This opens the Specify Stamp Duty Scale dialog. In the example shown, the Queensland
scales are in use.
PIA User Guide
Page 21
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
Specify Stamp Duty Scale dialog
Checking the Default check box selects the current stamp duty scale as the default scale, meaning that whenever you
create a new property file, the current stamp duty is the one used.
Any of the graduated scales may also be edited or set as a flat rate (that is, where the stamp duty is calculated based on
the total property price times the specified rate).
Any changes made to the stamp duty scale are saved in the PIA settings file when you quit PIA.
Clicking the Other radio button allows you to enter and save a customized stamp duty scale starting from scratch.
In Victoria, graduated scales are used up to
a value of $870,000. Properties above this
value revert to a simple flat rate for the entire
sum.
Northern Territory uses a formula to
calculate stamp duty. PIA simulates this
formula using a graduated linear scale.
New Zealand has no stamp duty on property
transfers, so selecting NZ changes stamp
duty to zero.
PIA User Guide
Page 22
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.4
Investments row
Purchase costs
Investments
Loan amount
$14,425
$40,000
$379,356
379,356
379,356
379,356
379,356
379,356
The value for Investments in the input column in the Investments row on the Investment Analysis spreadsheet shows the
total cash and equity investment at the time of purchase. Overwriting this value is a signal for PIA to allocate the amount
towards the property’s purchase price and to reduce the loan accordingly. If the amount exceeds the cost of the property,
the remainder is allocated towards purchase costs, renovation costs (if any) and loan costs (if any).If the amount exceeds
the total cost of purchase, the investment amount is reduced accordingly.
The values in the projection columns show projected investments for the selected years.
Investments dialog
Clicking on the Investments row title opens the Investments dialog.
The initial outlay is the total amount of cash (as opposed to borrowed funds) invested at the time of purchase.
Investments can be made toward the purchase price (that is, a cash deposit), associated purchase costs, renovation
costs (if any), and loan costs (if there is a loan). Investment can also take the form of equity: For example, where you
have already owned the investment for many years and want to evaluate the return on investment for a renovation of that
property. Use this field to ensure that the Loan amount is correct for an investment property that you already own.
Any cash (or equity) invested reduces the size of the loan. For example, paying the loan costs as an initial investment
results in a smaller loan which, in turn, reduces loan costs. PIA adjusts loan cost contribution in this case.
You can specify each cost item separately in the Investments dialog. Any entry in the Investments column results in a
corresponding reduction of the value shown in the Loan column. The sum of the two is shown in the Costs column.
The combined values in each row of the Investments and Loan column cannot exceed the value shown in the Costs
column. If the value is exceeded, PIA displays an error message, prompting you to review your entries in the appropriate
dialog.
If you choose to pay (rather than borrow) the Loan costs, PIA displays a message indicating that the Loan costs have
been reduced. The total investment is adjusted accordingly.
In the Investment Analysis spreadsheet, the values in the projection columns of the Investments row allow you to enter
additional cash outlays made during the years shown. These could include such things as principal payments on a
principal & interest loan or simply a cash payment to add a capital improvement.
PIA User Guide
Page 23
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
Annual Investments dialog
Clicking on any of the cells in the projection columns opens the Annual Investment dialog, revealing the derivation of the
total amounts shown.
If a credit line has been chosen as the loan type (see 3.5), the Credit Line Payments dialog opens showing the derivation
of the principal payments (see below).
Use the toolbar scroll buttons (“>”, “>>”,”<”, “<<”) to select the next, next five, previous, or previous five years.
Loan costs
To make changes to the loan costs,
click Loan costs in the Investments
dialog. This takes you to the Loan
Costs dialog described in 3.15.
After purchase
Additional cash outlays may also be
made in later years. These could be a
result of regular principal payments,
ad hoc lump sum payments (for
example, to pay for a renovation) or
(under a linked loans model available
in the Professional editions) from the
re-direction of ex-home loan
payments, (see 3.5), rental income
(see 3.9) and tax credits (see 3.5) to
repay the loan.
Renovations
If renovations are to be carried out in
later years, their cost can be specified
in the appropriate cell. The amount is
added to the loan and to the value of
the property and taken into account in
calculating building depreciation (see
3.13). If all or part of the cost of these renovations is paid in cash, it must appear as a cash outlay in the appropriate
Investments/Payments cell in the bottom half of the Annual Investment dialog.
Where a Principal & Interest loan has been chosen, the loan amount reduces over time in line with the principal
repayments shown in the Investments row of the spreadsheet. The Investments row title changes to
“Investments/principal” and the Interest row title changes to “Interest (P&I)”. This is described in 3.5.
Some items in this dialog are followed by a (c) or a (d):
(c) indicates that amounts in this row have been “capitalised” (i.e. borrowed)
(d) indicates that the amounts in this row have been used to repay debt.
Credit Line Payments dialog
If a credit line has been chosen as the loan type (see 3.5), clicking on any of the cells in the projection columns in the
Investments row opens the Credit Line Payments dialog showing the derivation of the principal payment.
PIA User Guide
Page 24
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.5
Loan amount row
Investments
Loan amount
Equity
$40,000
$379,356
$20,644
379,356
44,644
379,356
70,084
379,356
97,051
379,356
155,935
379,356
336,983
The value in the input column on the Loan amount row on the spreadsheet shows the total loan amount at the time of
purchase.
The values in the projection columns show the anticipated loan balance at the end of each year.
Loan Amount dialog
Clicking on the Loan amount row title on the Investment Analysis spreadsheet opens the Loan Amount dialog.
The initial loan amount represents the total of all costs (property price, purchase costs, loan costs etc.) less any initial
investments. If renovations were carried out immediately, the loan would account for these costs as well. Additional loans
may also be sought for items such as furniture or to help supplement initial cash flows.
The Equity Invested field allows you to enter the amount of equity in an investment property that you may already own.
For example, you might use this option if you have owned an investment property for many years and only wish to
analyse the current cash flows and/or are evaluating the investment return on major renovations that you wish to
undertake.
The values for Property cost, Renovation costs, Purchase costs and Furniture package are those entered in the
Investments dialog described in 3.4.
PIA User Guide
Page 25
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
Investment Loan Balance dialog
Clicking on any of the cells in the projection columns of the Loan amount row opens the Investment Loan Balance dialog,
which reveals the derivation of the totals shown. Annual loan balances are a function of initial balances, items added and
items paid. In general, the only item added would be the interest bill. If the rental expenses were to be capitalised, they
too would be added to the loan. If capital improvements (renovations) were carried out in later years, their cost would also
be added to the loan. Unless interest is capitalised (capitalise interest), it is usually the main payment. Other payments
could include principal payments (for a principal & interest loan), cash outlays (to pay for any capital improvements), exhome loan payments (if the loan is linked to a home loan), and rent and tax credits (if used to repay the loan principal).
Use the toolbar scroll buttons (“>”, “>>”,”<”, “<<”) to select the next, next five, previous, or previous five years.
Some items in this dialog are followed by a (c) or a (d):
(c) indicates that amounts in this row have been “capitalised” (i.e. borrowed)
(d) indicates that the amounts in this row have been used to repay debt.
PIA User Guide
Page 26
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
Loan costs
Clicking Loan Costs in the Loan Amount dialog displays the Loan Cost dialog (see 3.15).
Loan Interest & Type dialog
Clicking Loan Type in the Loan Amount dialog displays the Loan Interest & Type dialog.
The loan type can be either interest-only (I/O), principal & interest (P&I), interest-capitalised (c), credit line (CL) or a
combination of the above.
In the case of an Interest
Only loan, there are no
principal repayments in
the Investments row of
the spreadsheet, the
loan balance remains
static and the interest
payments are constant.
The row title in the
Investment Analysis
spreadsheet is “Interest
(I/O)”.
In the case of a Principal
& Interest loan is
chosen, the loan amount
reduces over time in line
with the principal
repayments shown in the
Investments row of the
spreadsheet. The
Investments row title in
the Investment Analysis
spreadsheet changes to
Investments/principal
and the Interest row title
changes to Interest
(P&I).
Where a loan is chosen for which the interest is borrowed (Capitalise Interest), there are no principal or interest
payments. The loan balance increases in line with the interest owed while investor’s cash flow is usually positive, being
made up of the net rental income and tax credits. The Interest row title changes to Interest (c).
Where a Credit Line has been specified (that is, one in which repayments are made in accordance with the investor's full
disposable income), (CL) appears in the row title. Under a Credit Line, PIA calculates the total income of the investor and
partner, their total committed expenditure (living expenses, mortgage payments and tax liabilities) and any extra is used
as additional principal payments off the loan. Examine the Credit Line Payments dialog (described in 3.4) to see how the
payments are derived.
Loan Type
The Loan Type checkbox provide an option to have split loans (two loans: Loan A and Loan B) or split rates (one loan
with two rates: Rate A and Rate B). If the checkbox is ticked, the total rate of interest is the sum of each rate in each
year, which in turn are specified in the Annual Rates dialog. If the checkbox is unticked, the total loan amount is the sum
of the loan amounts for the two loans. See http://www.somersoft.com.au/split_rate_loan.htm for an example.
Capitalised Interest
The financial model in PIA assumes that the additional interest on the increased loan balance (that is, the interest on the
capitalised interest) is not tax deductible. This default can be changed by ticking the Capitalised component taxdeductible check box at the bottom of the dialog. You should check with your accountant to see if this is the case in your
situation.
For further details of the uses of the Loan Interest & Type dialog, see 3.10.
Note that the most common use of a credit line is in accelerated mortgage reduction and this is usually in relation to a
non-deductible home loan rather than a tax-deductible investment loan. Where an investment loan and a home loan are
part of the same credit line, it is more effective (and usually more accurate in practice) to make the investment loan
interest-only and the home loan a line of credit.
PIA User Guide
Page 27
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.6
Equity row
Loan amount
Equity
Capital growth rate
$379,356
$20,644
6.00%
379,356
44,644
6.00%
379,356
70,084
6.00%
379,356
97,051
6.00%
379,356
155,935
6.00%
379,356
336,983
6.00%
The value in the input column on the spreadsheet shows equity at the time of purchase. The equity in each year is the
difference between the property value and the loan. It is not an input variable as PIA calculates the difference between
the property market value and the loan. Equity increases in line with the increasing value of the property and – in the
case of a principal & interest loan – with decreasing debt. If the property is sold (not recommended in the short term),
selling costs (sales commission and solicitor's fees) and Capital Gains Tax must be deducted to establish equity.
The values in the projection columns show the anticipated equity for the projected years displayed.
Equity dialog
Clicking on the Equity row title (or on any cell in the row) opens the Equity dialog.
This shows property value, outstanding loan, and the equity after 5- and 10-year periods.
It also shows projected selling costs (sales commission, solicitor's fees, etc), exit stamp duty (where applicable), capital
gains tax (where appropriate) and the corresponding after-sale equity if the property is sold at the projected market value.
If all the monies are borrowed, the initial equity is likely to be negative as the total cost of a property acquisition includes
purchase and loan costs in addition to the cost of the property itself. Equity increases with the increasing market value of
the property and with any decrease in the debt (for example in the case of a principal & interest loan).
Detailed Capital Gains Tax (CGT) calculations for the property are show in more detail in the Capital Gains Tax item in
the Reports and Calculator menus (see Chapter 7).
PIA User Guide
Page 28
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.7
Capital growth rate row
Equity
Capital growth rate
Inflation rate (CPI)
$20,644
6.00%
4.00%
44,644
6.00%
4.00%
70,084
6.00%
4.00%
97,051
6.00%
4.00%
155,935
6.00%
4.00%
336,983
6.00%
4.00%
The value in the input column on the Capital growth rate row title shows the anticipated average annual rate of growth of
the property market value. If you select and overwrite this value, the growth rate for all years is set to the new value.
The values in the projection columns show the anticipated capital growth rates for the individual years of the projection.
These values can also be selected and overwritten, in which case the change is reflected in the corresponding property
values.
Investment Property Growth dialog
Clicking on the Capital growth rate row title or double-clicking on any of the cells in the row opens the Investment
Property Growth dialog.
If you select and overwrite an individual property value in the Property Value row of the Investment Analysis spreadsheet,
the change is reflected in the Capital growth rate row of the spreadsheet and in the Capital growth rate dialog.
If you change the growth rate in a year of the Investment Property Growth rate dialog, subsequent years are changed to
the same rate.
Note that the spreadsheet cells do not behave in this way – a change to an annual rate does not effect subsequent years.
Entering a value into a Growth rates field resets all fields to this growth rate, including any individual property values
entered into the Property value row of the Investment Analysis spreadsheet
The years shown in the Capital Growth Rate dialog depend on the years currently displayed in the Investment Analysis
spreadsheet as defined in the Projections Displayed dialog described in 3.1.
Use the toolbar scroll buttons (“>”, “>>”,”<”, “<<”) to select the next, next five, previous, or previous five years.
PIA User Guide
Page 29
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.8
Inflation rate (CPI) row
Capital growth rate
Inflation rate (CPI)
Gross rent /week
6.00%
4.00%
$350
6.00%
4.00%
17,836
6.00%
4.00%
18,549
6.00%
4.00%
19,291
6.00%
4.00%
20,866
6.00%
4.00%
25,386
The value in the input column on the spreadsheet is the anticipated long-term average annual rate of inflation. In
Australia, this can be thought of as the rate of change in the Consumer Price Index (CPI).
The values in the projection columns show the anticipated inflation rate for the individual years of the projection. If you
overwrite a value for the inflation rate in the Inflation rate row of the Investment Analysis spreadsheet, only that year is
affected.
Inflation Rate dialog
Clicking on the Inflation rate (CPI) row title or double-clicking on any of the cells in the row opens the Inflation Rate dialog.
If you select and overwrite a value in the Inflation Rate dialog, the inflation rate for all subsequent years is set to the new
value.
Indexed Variables
PIA uses the inflation rate to project
rental income, rental expenses,
taxable income and living expenses.
These are referred to as indexed
variables, as their annual values
are indexed to the rate of inflation.
Use the toolbar scroll buttons (“>”,
“>>”,”<”, “<<”) to select the next,
next five, previous, or previous five
years.
Indexed
If a variable is not indexed (check
box not checked), it does not mean
that the variable does not change. It
simply means that any change to its
value in one year has no effect on
its value in later years.
If a value is not indexed, all
changes to that variable’s annual
values must be made by selecting
the variable (either in the
appropriate dialog or in the
spreadsheet row) and overwriting it.
From Year
When the Indexed check box is checked, the From Year field specifies the year from which the variable is indexed.
Earlier annual values remain static in their current state and can only be changed by selecting and overwriting.
Average Rate
This is the annual rate of inflation used to project the variable forward from the specified year. This may or may not be the
same rate as the CPI.
Linked to CPI
If the variable is linked to the CPI, the Average Rate is always set to the CPI rate (that is, a change in the CPI rate
results in the same change to that average rate for the variable). However, if you specify an Average Rate for the variable
different to that of the CPI rate, it is no longer linked to changes in the CPI rate (check box not checked).
If direct changes are made to any of the annual variables for rents, rental expenses, taxable income or living expenses, it
is assumed that the variable is no longer inflation-indexed up to that year. The variable is still inflation-indexed for the
remaining years of the 40-year projections.
PIA User Guide
Page 30
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.9
Gross rent/week row
Inflation rate (CPI)
Gross rent /week
Cash deductions
4.00%
$350
4.00%
17,836
4.00%
18,549
4.00%
19,291
4.00%
20,866
4.00%
25,386
The value in the input column shows the initial gross weekly, monthly or annual rental income, depending on the current
preference in the Rental Income dialog.
The values in the projection columns show the anticipated annual gross rents, after allowance is made for vacancies.
These values can be selected and overwritten, in which case annual values prior to this year remain static even if the
inflation rate is changed. (Rent projections are based on the inflation rate.)
Rental Income dialog
Clicking on the Gross rent/week row title or double-clicking on the value in the input column opens the Rental Income
dialog.
Most values can be selected and overwritten, however in the case of holiday letting, changes can only be made on a
seasonal basis in the Holiday Letting area of the Rental Income dialog.
Preferences
In the Rental Income dialog, the Preferences
area allows you to choose the way in which
rental income is specified: either by absolute
amount per week, month, year, or season
(holiday letting), or simply by gross yield.
Rental Income (1st year)
The Rent per week (or month or year) shows
the potential rent for the specified period.
The Potential annual rent represents the
corresponding annual rent (for example, the
weekly rent multiplied by 52).
The Annual vacancy rate is the anticipated
percentage of time that the property is vacant
(and not generating income).
The Actual annual rent is the anticipated rent
per year (potential annual rent less
vacancies).
The Gross yield is calculated by PIA as the
actual annual rent expressed as percentage
of the property value.
Holiday Letting
If you have a property where the rents
change seasonally (for example, a holiday
unit), click on the Holiday letting radio button
(Preferences) to enable the Holiday Letting
part of the Rental Income dialog. Here you can enter the duration (number of weeks), rent per week, and the anticipated
occupancy rate for each of the three seasons (peak, shoulder and off-season). The figures in the top half of the dialog
change to reflect the entries you make.
The Gross yield is calculated by PIA as the actual annual rent expressed as percentage of the property value.The Net
yield is calculated as the net rental income (actual annual rent less rental expenses) expressed as a percentage of the
property value.
PIA User Guide
Page 31
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
Annual Rental Income dialog
Clicking Annual Rent in the Rental Income dialog or double-clicking on any of the annual values in the Gross rent/week
row of the Investment Analysis spreadsheet opens the Annual Rental Income dialog.
This allows you to enter annual rent values
for individual years and to control how those
rent values are projected forward. If you
make a change to a particular year, the
values for subsequent years are still indexed
according to the settings in the Rental
Projections part of the Annual Rental Income
dialog.
Rental Projections
If the Inflation-indexed from year check box
is checked, rents are projected forward from
the year specified by the rate of inflation,
whilst rents for earlier years remain static
irrespective of subsequent changes to the
inflation rate. This does not mean that these
rents are made to be the same. If rents are
not indexed (check box not checked), all
annual rents remain static and any changes
made to the individual years do not impact
the annual rent rental income values of later years.
Use the toolbar scroll buttons (“>”, “>>”,”<”, “<<”) to select the next, next five, previous, or previous five years.
Advanced options
Clicking Advanced in the Rental Income dialog, opens the Advanced Options dialog.
Advanced Options dialog
This allows you to control the underlying financial model by redirecting income and expenditure to or from the cash flows
or the loan amount. Normally, all income (rents and tax credits) and expenditure (interest payments, rental expenses) are
accounted for in the cash flows associated with the investment. However, it may sometimes be useful to examine the
implications of modifying these defaults.
For example, in the case where a property is cash-flow positive, you may wish to use the rental income or tax credits to
make additional loan payments and reduce debt. Another example might be where you wish to internalise the investment,
that is, having all income paid into the loan and all expenditure added to the loan.
PIA User Guide
Page 32
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.10 Interest row
Cash deductions
Interest (I/O)
Rental expenses
8.00%
30.89%
30,348
5,621
30,348
5,846
30,348
6,080
30,348
6,576
30,348
8,001
In the Investment Analysis spreadsheet, the Interest row shows the type of loan, the interest rate and the projected
annual interest payments. The type of loan is reflected in the row title:
Interest (I/O)
Interest Only
Interest (P&I)
Principal & Interest
Interest (I/O, P&I)
Combination of Interest Only for a number of years followed by Principal & Interest
Interest (c)
Interest capitalised in loan
Interest (CL)
Credit Line
Interest (split)
Split loan (two parts)
The input column shows the average interest rate over the maximum term of the loan. If you select and overwrite this
value, the interest rate for all years for all loan components are set to the new value.
The values in the projection columns show the projected annual interest payments.
Any principal payments (for example, principal component of regular P&I repayments, ad hoc lump-sum repayments, or
principal payments via a credit line) are displayed in the Investments row (see 3.5).
Loan Interest & Type dialog
Clicking on the Interest row
title or double-clicking on
the interest rate in the Input
column opens the Loan
Interest & Type dialog, also
discussed in 3.4. This
dialog allows you to specify
exactly how the investment
is to be financed.
Interest Only
For an Interest Only loan,
the loan payment covers
interest only. The amount
owed (the principal)
remains constant.
Principal & Interest
In the case of a principal &
interest loan, the loan must
be completely repaid over
the term specified. Thus the
loan payments consist of
the interest owed plus a
contribution towards
reduction of the principal.
While the loan payments stay constant, the relativity of the two components changes over time: the loan interest
component decreases with the decrease in principal.
Capitalise interest
For a loan where the interest is capitalised (capitalise interest), no loan payments are made. The interest owed is simply
added to the loan balance: The loan balance and the loan interest components increase over time.
Under Australian tax law, if the interest on an investment loan is capitalised (that is, added to the loan), the interest on
the additional loan (that is, the interest on the interest) may not be tax deductible against other income. This is especially
the case where the rationale for capitalising the interest is to facilitate accelerated payments on linked non-deductible
home or personal loans (refer to Taxation Ruling TR 1998/22 and its Addendum (TR 1998/22A, August 2004) which is
available at Tax Offices, or via the Internet at www.ato.gov.au). You should consult your accountant or the Taxation
Office to determine the tax status in your situation.
Credit Line
For a credit line, all available monies are deposited in the loan (including salaries, etc) and personal living expenses are
drawn from it. Normally, this means that additional principal contributions are made in addition to regular principal &
interest payments. This accelerates mortgage reduction for this loan type.
PIA User Guide
Page 33
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
Split loans (Loan B)
The loan can be split into two separate components (Loan A and Loan B) with different loan types and interest rates.
Naturally, the total of the two loans equals the Total Loan.
Split rates (Rate A & B)
The Loan Type checkbox provides an option to have split loans (two loans: Loan A and Loan B) or split rates (one loan
with two rates: Rate A and Rate B). If the checkbox is ticked, the total rate of interest is the sum of each rate in each
year, which in turn are specified in the Annual Rates dialog. If the checkbox is unticked, the total loan amount is the sum
of the loan amounts for the two loans. See http://www.somersoft.com.au/split_rate_loan.htm for an example.
Annual Interest Rates dialog
Clicking Specify Annual Rates button on the Loan Interest & Type dialog or clicking the projected interest values in the
Investment Analysis spreadsheet displays the Annual Interest Rates dialog.
This dialog allows you to enter interest rates for individual years and individual loans.
The Interest and Principal rows under Loan A and Loan B show the interest paid and the principal repaid each year.
The Totals area at the bottom of the dialog shows the total principal remaining (Totals row), the total interest paid per
year (Interest) and the total payments per year (Payment).
The Average interest rate field shows the average interest rate for the period of the loan. Overwriting it resets the interest
rate for all years.
Use the toolbar scroll buttons (“>”, “>>”,”<”, “<<”) to select the next, next five, previous, or previous five years.
PIA User Guide
Page 34
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.11 Rental expenses row
Interest (I/O)
Rental expenses
Pre-tax cash flow
8.00%
30.89%
-$40,000
30,348
5,621
-18,134
30,348
5,846
-17,645
30,348
6,080
-17,137
30,348
6,576
-16,059
30,348
8,001
-12,963
The value in the input column of the Rental expenses row on the Investment Analysis spreadsheet represents the first
year’s rental expenses as a percentage of potential annual rent.
The values in the projection columns show the projected annual rental expenses. These values are derived from the sum
of normal recurring expenses and any special one-off expenses.
Rental Expenses dialog
Clicking on the Rental expenses row
title or the value in the Input column
opens the Rental Expenses dialog.
Normal Expenses (1st year)
This is an editable list of the individual
items that comprise the normal
expenses (rates, insurance,
maintenance, etc.) for the first year of
the investment.
By default, normal rental expenses
are projected annually by the rate of
inflation specified in the Inflation Rate
dialog discussed in 3.8. Expenses for
individual years can also be specified
directly by clicking the Annual &
Special Expenses button (described
below).
Agent’s Commission dialog
Agent’s commission can be entered as a figure or as a
percentage. To enter a percentage, click on Agent’s
Commission to display the Agent’s Commission dialog.
Annual Rent
The Annual rent shows the actual annual gross rent, (potential annual rent less vacancies).
Expenses/rent shows rental expenses as a percentage of the potential annual rent (i.e. assuming no vacancies).
Net rent is actual annual rent (i.e. excluding vacancies) less rental expenses.
Net yield shows the annual net rent as a percentage of the property value.
Clicking Annual Rent displays the Rental Income dialog discussed in 3.9.
Advanced
Clicking Advanced in the Rental Expenses dialog displays the Advanced Options dialog discussed in 3.9.
PIA User Guide
Page 35
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
Annual and Special Expenses
Clicking on Annual and Special Expenses in the Rental Expenses dialog opens the Annual and Special Expenses
dialog.
Annual and Special Expenses dialog
This dialog allows the entry of any special expenses incurred from time to time that are best treated separately from
normal indexed rental expenses . Such expenses are most commonly incurred in the first year as one-off expenses (for
example, quantity surveyor costs for assessing depreciable items).
By default, the normal annual expenses are indexed by the rate of inflation. If the expenses are changed for any specific
year, indexing is applied forward from that year. The values for previous years remain unchanged, but can be edited.
Use the toolbar scroll buttons (“>”, “>>”,”<”, “<<”) to select the next, next five, previous, or previous five years.
PIA User Guide
Page 36
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.12 Pre-tax cash flow row
Rental expenses
Pre-tax cash flow
Non-cash deductions
30.89%
-$40,000
5,621
-18,134
5,846
-17,645
6,080
-17,137
6,576
-16,059
8,001
-12,963
Pre-tax cash flow is the sum of all the monies that flow into and/or out of your pocket before tax is taken into account. In
most cases, this represents gross rent less interest, expenses and any payments toward the principal.
If you enter initial investments in the Investments dialog these are reflected in the input column of the Pre-tax cash flow
row. The projection columns show the projected pre-tax cash flow in selected years
Interest and expenses are not included in the cash flow if they are capitalised in the loan. Likewise, rents are not included
if they are used to directly reduce the loan.
Pre-tax Cash Flow dialog
Clicking on the Pre-tax cash flow row title displays the Pre-tax Cash Flow dialog. This box gives an overview of rent,
outlays, principal payments, interest and expenses for each projected year, plus the net pre-tax cash flow.
Use the toolbar scroll buttons (“>”, “>>”,”<”, “<<”) to select the next, next five, previous, or previous five years.
PIA User Guide
Page 37
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.13 Depreciation of building row
Non-cash deductions
Deprec.of building
Deprec.of fittings
2.50%
$24,000
5,000
3,850
5,000
5,043
5,000
3,487
5,000
1,795
5,000
526
The value in the input column on the spreadsheet shows the depreciation rate.
The values in the projection columns show the claimable depreciation allowance per year.
Depreciation of Building dialog
Clicking on the Deprec. of building row title opens the Depreciation of Building dialog.
Depreciation on the building (more correctly called capital allowance) represents the depreciation on all capital
improvements (including the original construction).
In some countries, such as New
Zealand, you have the choice of
depreciation methods (diminishing
value or straight line) and the
depreciation calculation is based on
the value of the improvements at the
time of purchase (see document
IR264 from www.ird.govt.nz).
In Australia, it is calculated using the
straight line method only and is based
on the original construction cost of the
building and the date on which
construction commenced, as
described below. In 2010, the NZ
moved to remove this as a tax
deduction.
Capital Costs
In order to provide reasonable defaults when creating a new property file, by default, the PIA assumes that the building
costs are 50% of the specified property price. You can change the building costs in the Building costs field to reflect the
appropriate amount on which the capital allowance is based. The Building costs as a % of property price field is then
updated and the link to property price is severed.
If the Link costs to property price check box is checked (default), the building costs change with any change to the
property price, using the percentage show in the Building costs as a % of property price field. If you do not want this link,
deselect the check box.
Capital Allowance
Total capital cost includes the building cost plus any renovation costs. You can enter the appropriate depreciation rate
into the Depreciation rate field (see notes below).
The Depreciation of building field shows the first year’s depreciation.
&
An in-depth treatment of all the changing rules and regulations governing depreciation is beyond the scope of this
publication, however, for more detail on depreciation rules and schedules, there are several booklets readily available to
property investors.
In Australia please see the booklets Rental Properties (NAT 1729) and Guide to Depreciation (NAT 1996), both of which
are published by the Australian Taxation Office and available from their website (www.ato.gov.au).
In New Zealand, please see the booklet Rental Income (IR 264) published by the New Zealand Department of Inland
Revenue and available from their website (www.ird.govt.nz).
PIA User Guide
Page 38
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.14 Depreciation of fittings (or chattels) row
Deprec.of building
Deprec.of fittings
Loan costs
2.50%
$24,000
$4,931
5,000
3,850
986
5,000
5,043
986
5,000
3,487
986
5,000
1,795
986
5,000
526
This row title is dependent on the default country setting you selected for PIA (Australia or New Zealand) in the Specify
Country Defaults dialog (see Chapter 2.3):
Deprec. of fittings
Deprec. of chattels
Australia selected
New Zealand selected
The term “fittings” (or “chattels” in New Zealand) refers to all items, excluding the building, that can depreciated for tax
purposes.
The value in the input column on the spreadsheet shows the total value of furniture and fittings (or chattels). Initially, the
amount has a default value of 6% of the property price (set at 6%), but this can be overwritten in the Depreciation of
Fittings dialog (see below).
The values in the projection columns show the corresponding total annual depreciation write-off for these items.
Depreciation of Fittings dialog
Clicking on the Deprec. of fittings row title opens the Depreciation of Fittings dialog, which contains an itemised
depreciation schedule and access to settings for how the depreciation is calculated in your situation.
Itemised
Depreciation
Schedule
The Furniture
package field
shows the value
(if any) entered
in the Property
Value dialog,
discussed in 3.2.
You can change
the names of
any of the other
items, listing
them
individually.
Alternatively,
you can group
them as
“General
fittings”.
Depreciation
Preferences
Depreciation is
calculated using
either the
diminishing
value method or
the prime cost
method. The
diminishing
value method confers tax benefits sooner and is thus more tax effective than the prime cost method. If an annual
depreciation schedule has already been prepared independently of the PIA program, it may be more convenient to
specify the annual total depreciation claims rather than the individual items. If this is done, the method is specified as
“Annual claim”.
The depreciation method can be set with the appropriate radio button at the bottom left of the dialog.
Link value to property price
By default, when the property price is first specified, the total value of fittings is set to equal 6% of this price and individual
items are all consolidated into the General fittings category (or split 50:50 with the Low-value pool). If changes are made
PIA User Guide
Page 39
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
to items the depreciation schedule, the Link value to property price check box is de-selected. Values of fittings then
remain unaffected by further changes in the property price.
Use “effective life” rules
In Australia, tax legislation dictates that, for items acquired after September 21, 1999, depreciation be defined in terms of
'effective life’ rather than a specified rate of depreciation. Under these rules, the rate at which items are written off is
equal to 150% (called the Diminishing Value Rate) divided by the years of effective life. This legislation also introduced a
low-value pool for items under $1000. These items are to be written off using the diminishing value method at a rate of
37.5% (effective life of 4 years), but at only half this rate (18.75%) in the year of acquisition.
For items acquired after May 9, 2006, the diminishing value rates were increased from 150% to 200% of the prime rate.
For example, an item with an expected life of 4 years is now written off at the rate of 50% per year rather than 37.5%.
This change does not apply to the low-value pool.
For details of depreciation rules in Australia and New Zealand, please see the references at the end of the previous
section.
PIA User Guide
Page 40
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.15 Loan costs row
Deprec.of fittings
Loan costs
Total deductions
$24,000
$4,931
3,850
986
45,806
5,043
986
47,224
3,487
986
45,902
1,795
986
44,706
526
43,876
The value in the input column of the Loan costs row title shows the total costs associated with financing the investment
property.
The amounts shown in the projection columns represent the amounts written off for tax purposes in the years specified.
Loan Costs dialog
Clicking on the Loan costs row title or anywhere in the Loan costs row opens the Loan Costs dialog.
Cost components
Loan costs are made up of several components,
some of which are a function of the size of the loan
while others may be a flat fee.
Loan costs vary between institutions, but as a
general guide expect total loan costs to be between
1 to 2% of the total loan (including costs). If loan
costs are paid at the time of purchase, this reduces
the size of the loan and in turn, the overall loan
costs.
If you enter a dollar amount in the Cost ($) column,
the Flat fee tick appears. It disappears if you enter
a percentage in the column % of Loan. In practice,
the first three items tend to be percentages, the
remainder tend to be flat fees.
Total costs shows the total costs as a percentage
of the total loan and as a dollar amount.
Total loan (including costs) shows the total loan
with the costs added. Here, the various cost
components can either be entered directly as a flat
fee or as a % of the loan.
Loan cost write-off period
Enter the appropriate figure into the Loan cost
write-off period field.
In Australia, these loan costs can be written off
over 5 years or the term of the loan, whichever is
the lesser. In New Zealand, loan costs are
considered a revenue cost and can be written off in
the year they are incurred.
PIA User Guide
Page 41
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.16 Total deductions row
Loan costs
Total deductions
Tax credit (single)
$4,931
$65,000
986
45,806
9,929
986
47,224
10,165
986
45,902
9,392
986
44,706
8,323
43,876
6,850
The input column of the Total deductions row remains blank as the purpose of this row is to show the total annual tax
deductions.
The projection columns show the projected annual tax deductions for the specified years.
Total Tax Deductions dialog
Clicking on the Total deductions row title opens the Total Tax Deductions dialog.
This shows the derivation of the total tax deductions. This is made up of both the "cash" deductions (interest, expenses)
and "non-cash" deductions (depreciation and loan costs).
Use the toolbar scroll buttons (“>”, “>>”,”<”, “<<”) to select the next, next five, previous, or previous five years.
There are two variations on the
norm:
1) Where purchase costs have
been specified as a revenue cost
(such as for properties purchased
in the ACT (see 3.3), these also
appear in the dialog, as shown in
the first example of the Total Tax
Deductions dialog.
2) Where interest has been
capitalised and the capitalised
component is deemed not
deductible, the capitalised
component is shown as being
subtracted from the Interest
deduction, as shown in the second
example of the Total Tax
Deductions dialog. (See 3.10
Interest row, Capitalize Interest)
PIA User Guide
Page 42
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.17 Tax credit row
Total deductions
Tax credit (single)
After-tax cash flow
$65,000
-$40,000
45,806
9,929
-8,205
47,224
10,165
-7,480
45,902
9,392
-7,745
44,706
8,323
-7,736
43,876
6,850
-6,113
The name of the row title changes to reflect how tax credits are calculated:
Tax credit
Tax credit (single)
Tax credit (joint)
Tax credit (super)
Tax credit (company)
Calculation based on a specified marginal rate
Calculation based on taxable income, property purchased by investor in a single name
Calculation based on taxable income, property purchased by two investors in joint names
Calculation based on taxable income for a property purchased a super fund
Calculation based on taxable income for a property purchased by a company
For details, see the description of the Tax Credits dialog below.
The value in the input column of the spreadsheet shows either the investor’s marginal tax rate, the investor’s taxable
income or the combined taxable income of the investor and partner.
The values in the projection columns show the projected tax credits in the specified years.
Tax Credits dialog
Clicking on the Tax credit row title opens the Tax Credits dialog.
Tax Credit Calculation
You can select the method
for calculating tax credits by
selecting the Use marginal
rate or Use taxable income
radio button.
Marginal rate gives a quick
approximation as it makes
the assumption that all tax
credits accrue at that rate of
tax. Taxable income gives a
more precise result as it
takes account of tax credits
accruing in different tax
brackets.
If Use marginal rate is
selected, the row title in the
Investment Analysis
spreadsheet is Tax credit
and the marginal rate is
shown in the input column
cell.
If the taxable income is
known, the tax credits can
be calculated more
precisely. If Use taxable
income is selected and the
title is registered in the
investor’s name only, the row title changes to Tax credit (single) and the input cell shows the investor’s taxable income.
If the property is registered in joint names, the row title becomes Tax credit (joint ) and the taxable incomes of both
partners are used to calculate the tax credits and the total is shown in the input column cell.
Ownership
Proportional ownership can be specified for a property purchased in joint names.
Using taxable income (in single or joint names) takes account of tax credits accruing in different tax brackets, thus
providing a more precise assessment of the tax credits. However, it requires knowledge of the current and future taxable
income, and assumes that tax brackets do not change. As a result of bracket creep, it may In some cases be more
accurate to use the marginal tax method for longer-term projections (10+ years).
PIA User Guide
Page 43
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
Taxable Income Projections
By default, taxable income for the first year is used to project taxable income for later years by indexing at the inflation
rate entered in the Inflation Rate dialog described in 3.8.
If the Inflation indexed check box is not checked, it means that changing a taxable income in any year has no effect on
the taxable income in other years. Note that it does not imply that taxable income remains constant from one year to
another.
If the Inflation indexed check box is checked and you enter a value for a particular year, subsequent years are still
automatically indexed at the rate indicated. If you make a manual change to the taxable income in a later year, that year
appears as the base year for any further automatic indexing.
Advanced
Clicking Advanced opens the Advanced Options dialog described in 3.9.
Tax Benefits dialog
Clicking on items in the Tax Credits row opens the Tax Benefits dialog.
Property ownership
You can enter the
relative shares of
investor and partner in
the property ownership
in the Property
ownership row.
Selecting the Single
name radio button in
the Property
ownership area, gives
100% ownership to the
investor.
Number of properties
You may vary the
number of properties
for which you wish to
calculate potential tax
benefits by changing
the values in the
Properties/number
area.
Changing the
number of properties
in the dialog does not
feed back to the spreadsheet. It is simply a means of checking the total tax benefits of multiple properties.
Taxable income and tax credits
New taxable income equals present taxable income minus rental deductions. When you enter the present taxable income
of both investor and partner, the dialog may then be used to compare the level of tax savings (tax credits) that may result
from the different numbers of investment properties.
Tax Options: Quarantine losses
Choosing Quarantine losses results in all tax losses being carried forward and offset against future profits. They will not
be claimed against other income (eg salary) in the same year they occur.
Tax Options: NRAS
Choosing NRAS results in tax credit subsidies and discounted rental income for a number of years as specified in the
NRAS Options dialog (Settings menu)..
Report button
Clicking on the Report button closes the dialog and generates a Tax benefits report based on current taxable incomes
and current tax rates.
PIA User Guide
Page 44
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.18 After-tax cash flow row
Tax credit (single)
After-tax cash flow
Rate of return (IRR)
$65,000
-$40,000
15.27%
9,929
-8,205
10,165
9,392
8,323
-7,480
-7,745
-7,736
Your cost/(income) per week
6,850
-6,113
The After-tax cash flow row displays the monies that flow into or out of your pocket after tax is taken into account. In most
instances, the figures simply represent the pre-tax cash flows adjusted for any tax credits or tax payments.
The input column on the spreadsheet shows any cash flow at the time of purchase (usually an initial cash deposit).
The projection columns show the annual projected after-tax cash flows for the specified years. Negative values (shown in
red in the spreadsheet) represent investments made in the property while positive values (shown in black) represent cash
dividends received as a result of the investment.
After-tax Cash Flow dialog
Clicking on the After-tax cash flow row title, or anywhere in the row itself, opens the After-tax Cash Flow dialog. This
shows how the after-tax cash flows have been derived from the annual rental income less any outlays, principal
payments, interest and other expenses. Where appropriate, the tax credits are then added, resulting in the annual aftertax cash flow.
The cost per week (after-tax cash flow divided by 52) is given in the bottom row.
If you have specified that tax credits be used to reduce the investment loan through additional principal repayments (see
3.4 and 3.5) the after-tax cash flow is the same as the pre-tax cash flow.
Note that in this case, tax refunds are credited for the year on which they are based.
Use the toolbar scroll buttons (“>”, “>>”,”<”, “<<”) to select the next, next five, previous, or previous five years.
PIA User Guide
Page 45
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.19 Rate of return (IRR) row
After-tax cash flow
Rate of return (IRR)
Pre-tax equivalent
-$40,000
15.27%
23.13%
-8,205
158
-7,480
-7,745
-7,736
Your cost/(income) per week
144
149
149
-6,113
118
This item is one of the measures used to evaluate the investment. The Internal Rate of Return (IRR) is a measure of the
return on a series of cash flows where the time factor is taken into account. On the spreadsheet, the input column shows
the IRR over the projected years of the investment. It is calculated by taking into account all of the after-tax cash flows
(negative values represent your investments) and your equity at the end of the period.
Internal Rate of Return dialog
Clicking on the Rate of return (IRR) row title opens the Internal Rate of Return dialog.
Total investment
This shows the total amount (adjusted by inflation to
convert it to today’s dollars) invested by you over the
projected period.
Equity at end
This shows your equity in the property at the end of
the term, both in nominal and today’s dollars.
Net present value (NPV)
The Net Present Value (NPV) represents the total
value of your investment less the total cash that you
have invested, all adjusted to today's dollars.
Real Return
This shows the effective interest on your investment,
that is, the after-tax, after-inflation rate of return. This
measure takes into account the buying power of the
money made.
The year displayed in the right-most column of the
Investment Analysis spreadsheet determines the
range of years over which the Internal Rate of
Return is calculated.
This can be changed in the Projections Displayed
dialog described in 3.1.
If the Real return is undefined, the value shown is
“?????” (or the value set by you in the Preferences
dialog (see 7.7).
In our example, the Investment Analysis spreadsheet shows a projection over 10 years, so the Rate of Return shown
near the bottom of the input column is the Internal Rate of Return calculated over 10 years. Note also that the inflation
rate shows the value entered in the Inflation Rate dialog described in 3.8.
To understand the Internal Rate of Return, think of the money you are outlaying on your investment property as being
deposited in a bank account, with the interest added each year. In this case the "Deposits" are represented as the aftertax cash flows. The total amount in your "account" (including interest) at the end of the period is the equity in the
investment property. The IRR represents the effective "interest rate" that you have received, but with one important
difference: because the interest remains in the property, it is not taxed. The internal rate of return is also calculated and
presented for the case where the property is sold and where selling costs and capital gains tax are taken into account.
PIA User Guide
Page 46
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.20 Pre-tax equivalent row
Rate of return (IRR)
Pre-tax equivalent
15.27%
23.13%
158
Your cost/(income) per week
144
149
149
118
This item is related to the Internal Rate of Return (IRR) and is used for comparison with cash investments.
Whereas the IRR gives the after-tax rate of return, this cell displays the equivalent before-tax rate of return using the
marginal tax rate to make the adjustment. The Pre-tax equivalent of the Internal Rate of Return provides a useful
comparison with cash investments, as this is the interest rate that you would need to achieve from a bank to get the same
after-tax return. Any such bank interest would be taxed at your marginal rate.
This is not to be confused with the Pre-Tax Internal Rate of Return, which is the return calculated on your pre-tax cash
flows.
Pre-tax Equivalent of IRR dialog
Clicking on the Pre-tax equivalent row title or input column opens the Pre-tax Equivalent of IRR dialog.
This itemises both pre-tax cash flow and equity, and shows pre-tax IRR, after-tax IRR, marginal tax rate and pre-tax
equivalent of IRR.
In the example, the after-tax IRR is 13.32% while the pre-tax equivalent is shown as 23.57%. In other words, to achieve
the same after-tax return (13.32%) on your cash investments, you would have to invest your money at 23.57%.
There are several options for displaying useful investment indices as the “bottom line” for the investment. This row can be
replaced with Net present value or the Real IRR (that is, inflation adjusted). For details, see the Preferences option in the
Settings menu in Chapter 7.
PIA User Guide
Page 47
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.21 Your cost/(income) per week
Rate of return (IRR)
Pre-tax equivalent
15.27%
23.13%
158
Your cost/(income) per week
144
149
149
118
This is literally the bottom line, the reason for all the entries and calculations in the spreadsheet: It shows the projected
weekly net cost or income resulting from the investment.
The default title is Your cost/(income) per week. Cost is shown without brackets, income in brackets.
If you prefer to reverse the convention (see 7.7), you can set the title to Your income/(cost) per week with income shown
without brackets and costs shown in brackets.
PIA User Guide
Page 48
PIA Version 7.320
Chapter 3
Investment Analysis spreadsheet
3.22 Property Details area
The Property Details area at the bottom of the Investment Analysis spreadsheet key figures for the property.
“Cash-neutral investment” is the initial deposit required to make the after-tax cash flow in the first year equal to zero. If
the investment is already cash positive when there is no deposit, this variable is shown as $0.
“Cash positive by” is the year in which the after-tax cash flow is positive for the first time.
The Gross yield is calculated by PIA as the actual annual rent expressed as percentage of the property value.
The Net yield is calculated as the net rental income (actual annual rent less rental expenses) expressed as a percentage
of the property value.
PIA User Guide
Page 49
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
This spreadsheet has several aims but the primary one is to provide a tool to help people examine ways of owning their
own home sooner. In contrast to an investment loan, the interest on your own home loan is not tax deductible and thus
paying off the loan as fast as possible usually makes financial sense. For most property investors, this is generally the
first step towards building a portfolio of investment property as the increased equity in your own home provides very
convenient collateral when you are looking for finance to purchase your first investment property.
In keeping with the primary focus of the spreadsheet, the “bottom line” displays the remaining term of the loan (at the
bottom of the Input Column) and the net cost per year, which usually represents the annual loan repayments. PIA
assumes that the loan is of a principal and interest type with loan payments paid monthly in arrears.
This spreadsheet has several useful applications
•
You can use the spreadsheet to simulate the current home loan by specifying the amount of debt still owing together
with the current repayments and interest rates. You are then at liberty to assess the impact on the term remaining of
making additional regular or irregular payments. The spreadsheet shows you both the data you have entered and the
projections based on that data (or based on default values, if these have not been changed).
•
You can also use the spreadsheet to examine the impact of consolidating all your non-deductible loans (e.g. home,
car and personal loans) and refinancing them into the one loan with a different term and interest rate. The aim here
might be to reduce the cash flow burden rather than to reduce the remaining loan term. Another example might be to
examine the use of a credit line facility where all available monies are deposited and only living expenses redrawn.
This would give you an idea of just how fast you could possibly repay the loan.
•
This spreadsheet is also an important precursor to using the Linked loans Analysis spreadsheet (see Chapter 6) and
the Wealth Builder spreadsheet (see Chapter 5) as both rely on you having simulated your current home loan debt
and repayment schedule.
This chapter describes each of the variables of the Home Loan Analysis spreadsheet (and their accompanying dialogs)
row by row (from top to bottom).
You can open the spreadsheet by clicking on the triangle-shaped icon in the toolbar.
The Home Loan Analysis spreadsheet has the same basic layout as the Investment analysis spreadsheet.
For details of the user interface, please see Chapter 2.
PIA User Guide
Page 50
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.1
Year row
Home Loan Analysis
End of year
Home value
John Doe – for personal use only
Input
Projections over 10 years
2013
1yr
2yr
3yr
5yr
$480,000 508,800 539,328 571,688 642,348
10yr
859,607
In order for PIA to collate and synchronise information from both investment and home loans, the Year row is common to
all PIA spreadsheets (see 3.1). As the primary focus of PIA is the Investment Analysis spreadsheet, the year in the Input
column usually represents the year of purchase of the investment property which, by default, is set to the current year.
The Home Loan Analysis spreadsheet is normally synchronised to the same year, with the Amount owing to be set
accordingly. You may, however, simulate your home loan from the year it was originally obtained, but you should be
aware that information you record for the investment property may not synchronise with the home loan and is probably
best kept as a separate file.
The values in the projection columns specify the years for which projected values are displayed. The maximum value is
40 years. A value of zero results in no information displayed in that column. The years do not need to be consecutive or
even in chronological sequence. To change any year, simply select the particular cell and overtype it. Note, however that
if you change a year, PIA changes it in all spreadsheets.
Set Projections dialog
Clicking on the Year row title opens the Set Projections dialog, allowing you to select the range of years displayed. (For
details about setting projections, please see 3.1.). You can also overwrite the values in the Year row projections columns.
PIA User Guide
Page 51
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.2
Principal residence row
End of year
Home value
Capital growth rate
2013
$480,000
6.00%
1yr
508,800
6.00%
2yr
539,328
6.00%
3yr
571,688
6.00%
5yr
642,348
6.00%
10yr
859,607
6.00%
The value in the input column of the Principal residence row represents the value of your home for the year specified
above.
The values in the projection columns show the anticipated increase in the value of the investor’s home over the
projection period. PIA calculates these using the specified sequence of annual capital growth rates. However, the
property value in any year can be selected and overwritten, in which case PIA recalculates the corresponding Capital
growth rate parameter.
PIA uses the value of the investor’s home to calculate the loan value ratio (see 4.17), an index often used by banks to
determine your eligibility for refinancing the loan.
Home Value Growth dialog
Clicking on the Principal Residence row title (or double-clicking on the value in the input column) opens the Home Value
Growth dialog. This dialog allows you to change future property values by entering them in the Property value line and
growth rates by entering them in the Growth rates line. Values may be set for any year up to 40 years. (For details of
setting projections, please see 3.1.)
It is not necessary to obtain an official valuation on the value of your property. A phone call to a reputable real estate
agent is sufficient to establish an approximate value for your own home.
PIA User Guide
Page 52
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.3
Capital growth rate row
Home value
Capital growth rate
Home equity
$480,000
6.00%
$144,000
508,800
6.00%
181,811
539,328
6.00%
222,098
571,688
6.00%
265,026
642,348
6.00%
359,529
859,607
6.00%
656,019
The value in the input column on the Capital growth row title shows the anticipated average annual rate of capital growth
of the investor’s home over the range of annual projections. If you select and overwrite this value, PIA sets the growth
rate for all years to the new value.
The values in the projections columns show the anticipated capital growth rates for the individual years of the projection.
These values can also be selected and overwritten, in which case the change is reflected in the corresponding values of
the investor’s home.
Home Value Growth dialog
Clicking on the Capital growth row title (or double-clicking on the value in the input column or projections columns) opens
the Home Value Growth dialog (see 4.2). This dialog allows you to enter property values/ and or growth rates for any year
up to over the maximum projection of 40 years.
If you change a property value for a particular year, PIA recalculates the growth rate for that year. Growth rates for
subsequent years remain unchanged.
PIA User Guide
Page 53
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.4
Equity row
Capital growth rate
Home equity
Amount owing
6.00%
$144,000
$336,000
6.00%
181,811
6.00%
222,098
6.00%
265,026
6.00%
359,529
6.00%
656,019
Home equity is the difference between the value of the home (principal residence) and the outstanding loan amount at
any point in time. Ideally, equity increases as your home value increases and the loan is repaid.
The value in the input column on the Home equity row title shows the current equity in your principal place of residence.
The values in the projections columns show the projected equity in the home in the selected years.
Home Equity dialog
Clicking on the Home equity row title (or on any cell in the row) opens the Home Equity dialog. This shows the derivation
of the equity projections (projected value less projected loan balance) for up to forty years.
For details about how to set projections, see 3.1.
PIA User Guide
Page 54
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.5
Amount owing row
Home equity
Amount owing
Refinance costs
$144,000
$336,000
$0
181,811
222,098
265,026
359,529
656,019
The value in the input column of the Amount owing row normally represents the total amount owing on the loan for your
home. If this is the case, it is possible to change the amount by selecting it with the mouse and over-writing it. The
amount must correspond to the end of the year shown at the top of the input column (see 4.1), usually the current year.
However, as outlined below, the amount may represent not just a home loan balance, but a consolidation of nondeductible loans refinanced into the one mortgage.
Loan Consolidation dialog
Clicking on the Amount owing
row title (or clicking or doubleclicking the value in the input
column) opens the Loan
Consolidation dialog. This
facility allows you to consolidate
and refinance all your nondeductible principal and interest
loans into the one mortgage
package with your home loan.
Current Principal & Interest
Loans
For each of your loans, enter
the principal (original amount of
the loan), the original interest
rate, the term of the loan, the
amount owing in the appropriate
columns in the Current P&I
Loans area.
Pressing the Enter key then
prompts PIA to calculate the
corresponding monthly payment
and the remaining term for each
of the loans (right-hand column)
and to display the total amount
owing and total repayments in
the Current totals line of the
Current P&I Loans area
Refinance costs
If you are refinancing the total
loan and have associated
finance costs, click on the Set Finance Costs button (see 4.6) and ensure that the Refinance not applicable check box is
unchecked. If you are not refinancing, check the Refinance not applicable box and they are shown as zero.
Repayments
Consolidation and refinancing is normally done to save on interest costs or to reduce the repayment burden. If you wish
to continue make the same total repayments and thereby reduce the remaining term and save on interest costs, check
the Maintain current payments check box. To reduce the repayments, simply specify a new lower monthly payment.
Refinanced Home Loan
In the Refinanced Home Loan area, the Current amount owing line shows the sum of the amounts from each of the
Principal & Interest loans listed above. If you check the Refinance not applicable check box, the Refinance costs line is
set to zero. If you do not check the Refinance not applicable check box, the Refinance costs field in the dialog shows the
total refinance costs specified in the Home Loan Refinance Costs dialog which opens when you click on the Set
Refinance Costs button. (This is fully described in 4.6 below). The Total amount owing line includes any refinance costs
if applicable. The New monthly payment line shows the sum of the monthly payments listed above (if you check the
Maintain current payments check box) or you can specify them as required.
On return to the Home Loan Analysis spreadsheet, PIA uses the New monthly payment to recalculate the remaining term
of the loan for the Total amount owing and the interest rates specified on the spreadsheet. If the repayment is less than
the interest, the loan would never be repaid and the term remaining is undefined (that is, it shows as '?????’).
PIA User Guide
Page 55
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.6
Refinance costs row
Amount owing
Refinance costs
Repayments
$336,000
$0
The value in the input column of the Refinance costs row is zero if there is no refinancing involved, otherwise the value is
the sum of the individual components that make up the cost of refinancing (e.g. establishment fees, mortgagee stamp
duty, etc). This amount is added to the Amount owing to give the current Loan balance.
Home Loan Refinance Costs dialog
Clicking anywhere in the row opens the Home Loan Refinance Costs dialog. This can also be accessed via the Set
Refinance Costs button in the Loan Consolidation dialog (see 4.5).
Here you may override the PIA defaults and specify the various loan cost components and their values.
Cost components
Loan costs are made up of several components, some of which are a percentage of the size of the loan while others may
be a flat fee.
If you enter a dollar amount in the Cost ($) column, the Flat fee tick appears. It disappears if you enter a percentage in
the column % of Loan. In practice, only the first three items tend to be percentages, the remainder tend to be flat fees.
The Total costs line shows the total loan costs as a percentage of the total loan and as a dollar amount.
The Total loan line shows the total loan excluding any refinancing costs.
Refinance costs
If, after entering the values for the refinancing costs, you decide to apply these to the loan, check the Apply to loan check
box. The Total loan line now includes these refinance costs. When you click OK, PIA recalculates the term remaining for
the new loan balance.
If you do not check the Apply to loan check box, the costs are not added to the loan (and have no effect).
PIA User Guide
Page 56
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.7
Regular Repayments row
Repayments
Regular (mth/yr)
Extra (mth/yr)
2963.77
0
35,565
0
35,565
0
35,565
0
35,565
0
35,565
0
PIA allows you to take regular monthly, extra monthly and irregular annual loan payments into account in repaying your
home (or consolidated) loan. These types of payment are covered by the next three rows of the spreadsheet.
The value in the input column represents the regular amount repaid monthly. The amounts shown in the projections
columns show the amounts repaid each year.
Home Loan Repayments dialog
Clicking on the Regular (mth/yr) row title (or double-clicking on the value in the input column or clicking a value in a
projections columns) opens the Home Loan Repayments dialog. This shows a summary of all payments and redraws
based on entries in the three repayments and three redraws rows. The various types of repayment and redraw are
described in the following sections.
Credit line check box
Clicking the Use credit line to repay home loan check box turns the Credit line option on or off (see 3.8 and/or 7.7).
PIA User Guide
Page 57
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.8
Extra repayments row
Regular (mth/yr)
Extra (mth/yr)
Irregular
2963.77
0
35,565
0
0
35,565
0
0
35,565
0
0
35,565
0
0
35,565
0
0
The value in the input column shows the extra monthly loan repayment and the values in the projections columns show
the equivalent annual amounts repaid in the specified years. This changes if a credit line has been specified. Under a
credit line, all after-tax income is channelled into the mortgage account and living expenses are redrawn from that
account. When you select a credit line the following occurs:
•
The input column of this row is blank (an extra monthly payment is not applicable under a credit line)
•
PIA calculates the annual values in the projection columns based on your total after-tax income less the regular
payments
•
The row title changes from “Extra (mth/yr)” to “Extra (credit line)”
•
The projected living expenses appear as redraws until the loan is repaid
You may select Credit Line under the Settings menu on the spreadsheet or the Credit line toolbar icon (see 7.7).
Extra Payments dialog
Clicking on the Extra (mth/yr) row title (or on a value in a projections
columns) opens the Extra Payments dialog. You can enter extra
regular payments here.
Credit line
A Credit line means that all salary and wages are paid directly into
the loan and living expenses are taken out as redraws (see below).
This can speed repayment, for the following reasons:
You have to draw the money to spend it. Instead of paying in what
you can afford, you take out what you need.
The Credit Line encourages you to draw up a budget – which
increases awareness of spending behaviour.
If you have a loan and a current or savings account, surplus cash
earns little interest. With a credit line, "surplus" cash helps reduce
your interest bill. This can be done by checking the Use all available
cash for payments check box.
Please note that income is specified in the Current Income dialog in
the Investor menu and living expenses are specified in the Living
Expenses dialog in the Investor menu. For further details, please see
Chapter N.
Regular Payments
In the Regular Payments area, the Regular monthly payment is
normally the scheduled amount to be paid monthly in arrears so that
the entire loan is repaid over a pre-determined number of years.
The Extra monthly payment line enables you specify an extra
monthly payment in order to repay the loan sooner than originally
scheduled.
Fortnightly Payments
If you want to know the effect of paying half of the monthly payment each fortnight rather than one payment per month,
you can simulate the fortnightly payment schedule by making an additional payment each month of one twelfth of a
normal monthly payment (it is equivalent to making 13 monthly payments – one more per year than normal). Click on the
Add to extra button to add 1/12 of a regular payment to the Extra monthly payment field. Click the Remove from extra
button to remove the payment again.
As PIA uses a monthly time step for calculations, the result is only a close approximation – the interest saved by making
the earlier payment of half the monthly amount is not taken into account.
PIA User Guide
Page 58
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.9
Irregular repayments row
Extra (mth/yr)
Irregular
Total repayments
0
0
0
35,565
0
0
35,565
0
0
35,565
0
0
35,565
0
0
35,565
You can enter irregular (that is, ad hoc, unscheduled) payments into the appropriate projections column by selecting the
default value and overwriting it. PIA assumes that the amount entered is to be paid into the loan account at the end of the
year and recalculates the term remaining when you press the Enter key.
4.10 Total repayments row
Irregular
Total repayments
Redraws
0
35,565
0
35,565
0
35,565
0
35,565
0
35,565
This rows shows the sum of all the various repayments (regular, additional and irregular) itemised in the Loan
Consolidation dialog (see 4.5).
PIA User Guide
Page 59
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.11 Regular redraws row
Redraws
Regular (mth/yr)
Irregular
0
0
0
0
0
0
0
0
0
0
0
The value in the input column of the Regular redraws row shows the regular monthly withdrawals from the loan account
loan. The values in the projections columns show the equivalent annual amounts withdrawn in the specified years.
Technically they act as extra borrowings but in practise they are used as a means of budgeting for things such as normal
living expenses and counterbalance any extra repayments specified above (see 4.8).
Under a credit line, all after-tax income is channelled into the mortgage account and living expenses are redrawn from
that account. When you select a credit line, the following occurs:
•
The input column of this row is blank (a fixed regular monthly redraw is not applicable under a credit line),
•
The projected living expenses appear as redraws until such time as the loan is repaid
•
The row title changes from “Regular (mth/yr)” to “Regular (living exp)”,
•
The Extra repayments row shows your total after-tax income less the regular payments (see 4.8)
For regular monthly redraws, enter the value into the input column in the Regular row. PIA then shows the annual
redraws in the projections columns of the row.
For irregular (that is, ad hoc unscheduled) redraws, enter the value directly into the spreadsheet in the year in which the
redraw is made.
Annual Living Expenses dialog
Clicking anywhere in the row when a credit line has been selected opens the Annual Living Expenses dialog.
Here, you can specify the total annual living
expenses in any year or specify the rate at
which these might change with time. Your
living expenses for the first year can be
entered via the Annual Living Expenses
Budget dialog (For further details see Living
Expenses under the Investor menu,
described more fully in 7.4).
Living expense projections
By default, annual living expenses are
assumed to increase in line with inflation.
You can over-ride this assumption and
specify your precise living expenses for any
year. Note that unless you uncheck the
Inflation indexed from year check box, the
remaining years continue to increase by the
specified rate.
In the year in which the loan is repaid, the redraw column may show a much lower figure than the previous years. This is
because redraws are only made until the loan is fully cleared repaid. The loan account then goes into credit – or is closed
by the borrower.
PIA User Guide
Page 60
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.12 Irregular redraws row
Regular (mth/yr)
Irregular
Total redraws
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
You can enter irregular (ad hoc unscheduled) redraws for projected years directly into the appropriate projections
columns on the spreadsheet. Such entries do not affect redraws in other years.
4.13 Total redraws row
Irregular
Total redraws
Interest rates
8.00%
0
0
8.00%
0
0
8.00%
0
0
8.00%
0
0
8.00%
0
0
8.00%
The Total redraws row shows the annual projected totals of all redraws. Because this row contains calculated fields only,
no entries can be made here.
4.14 Interest rates row
Total redraws
Interest rates
Interest
8.00%
0
8.00%
26,554
0
8.00%
25,806
0
8.00%
24,996
0
8.00%
23,169
0
8.00%
17,097
The Interest rates row allows you to specify the annual interest rates applying to the home loan year by year.
The value in the input column shows the average annual interest rate over the term of the loan. If you select and
overwrite this value, the interest rates for all years are set to the new value.
The values in the projections columns show the interest rate for the specified years. If you select and overwrite any of
these values, PIA recalculates the average rate but the interest rates in other years are unaffected.
Interest Rates dialog
Clicking on the Interest rates row title opens the Interest Rates dialog. As in the spreadsheet cells, you may overwrite the
average interest rate or the rate for individual years. Note, however, that in this case, making a change to any one year
results in the same change to all following years. You can enter rates for each year up to 40 years. (See 3.1 for details on
how to set projections)
PIA User Guide
Page 61
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.15 Interest row
Interest rates
Interest
Loan balance
8.00%
$336,000
8.00%
26,554
326,989
8.00%
25,806
317,230
8.00%
24,996
306,661
8.00%
23,169
282,819
8.00%
17,097
203,588
The Interest row shows the projected total interest component payable each year. PIA calculates these values, so no
entries can be made here.
Note that unlike the interest on investment property loans, PIA does not treat the interest on the loan for your home as
tax-deductible, even if you were to use an investment property as collateral for the loan. The important issue in the eyes
of the tax office is the purpose of the loan, not what is used as security for it.
4.16 Loan balance row
Interest
Loan balance
Loan value ratio
$336,000
70.00%
26,554
326,989
25,806
24,996
23,169
317,230 306,661 282,819
Your net cost per year
17,097
203,588
The values in the input column of the Loan balance row shows the amount still owing on the loan on your home at the
end of the current year. The values in the projections columns show the amount owing for each of the years displayed,
taking into account all repayments and redraws. Note that at the outset, this may include refinance costs if the loan
represents a consolidation of several principal and interest loans (see 4.5).
Home Loan Balance dialog
Clicking on the Loan balance row title (or double-clicking on the value in the input column) opens the Home Loan Balance
dialog. This box explains how the balance of the loan is derived, taking interest, redraws and repayments into account.
The annual loan balances are a function of initial balances, items added and items paid. In general, the only item added
is the interest bill, if you also have a credit line, redraws are shown here (see 4.11, 4.12).
Items paid are generally just the regular home loan payment, but you may also be paying an additional amount per
month, or even making irregular “one-off “ payments (see 4.8, 4.9). (See 3.1 for details on how to set projections)
PIA User Guide
Page 62
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.17 Loan value ratio row
Loan balance
Loan value ratio
Term remaining
$336,000
70.00%
17.7yr
326,989
35,565
317,230 306,661 282,819
Your net cost per year
35,565
35,565
35,565
203,588
35,565
The loan value ratio is the ratio of the loan to the value of the property. It is an index used by lenders as a measure of risk
in lending you the money. For example it is common for lenders to charge for mortgage insurance if the loan to value
ratio exceed 80%.
The figure in the input column on the Loan value ratio row is the current loan value ratio.
Home Loan Value Ratio dialog
Clicking on the Loan value ratio row title or on the value in the input column) opens the Home Loan value ratio dialog.
The two examples show how the loan value ratio alters over time as the value of the property increases and the home
loan balance is reduced.
PIA User Guide
Page 63
PIA Version 7.320
Chapter 4
Home Loan Analysis spreadsheet
4.18 Term remaining row
Loan value ratio
Term remaining
70.00%
17.7yr
35,565
Your net cost per year
35,565
35,565
35,565
35,565
The input column of the Term remaining row displays the number of years remaining to pay out the loan for your home
completely. The remainder of the row displays the annual net cost (see 4.19).
For the loan to be repaid, the repayments must be greater that the interest being charged by the lender. If the
repayments are not sufficient to meet the interest bill, the loan cannot not be repaid and the term remaining is not defined
and is displayed as ?????, unless you have chosen another string for undefined values (Preference command in the
Settings menu, 7.7)
4.19 Your net cost per year
Loan value ratio
Term remaining
70.00%
17.7yr
35,565
Your net cost per year
35,565
35,565
35,565
35,565
The projections columns in this area show the net sum of all the loan repayments and redraws for each year. By default,
these are simply the regular scheduled loan repayments and remain constant until the loan is repaid. However, where
you use a credit line, or where ever extra payments and/or redraws are specified, these indicate the total net cost.
Negative values are shown in red with a minus sign. A negative net cost indicates that redraws exceed repayments, so
the loan is increasing. PIA displays a warning message if you enter a value that creates this situation.
PIA User Guide
Page 64
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
This chapter provides an overview of the Wealth Builder spreadsheet and its related dialogs. The term “wealth builder”
refers to the main application of this spreadsheet, which is to provide an interactive analysis of each step in accumulating
a portfolio of investment properties (building wealth). The Wealth Builder spreadsheet helps you to address the question:
“When can I take the next step and buy another investment property?” The answer is “when you can afford to and/or
when a bank would lend you the money”, both of which are indicated in the spreadsheet.
This spreadsheet draws together the information from both the Investment Analysis spreadsheet (Chapter 3) and the
Home Loan Analysis spreadsheet (Chapter 4) to give a more holistic guide to building wealth through a portfolio of
investment properties. You simply need to specify the total number of investment properties (including those in any
portfolio you have created) accumulated at any time and the spreadsheet analyses
Whether you can afford them
Whether a bank is likely to finance them
Equity accumulation
&
To function as a relatively simple and interactive tool, the Wealth Builder spreadsheet assumes that any additional
investment properties in the hypothetical portfolio are all derived from projected values of the property defined in the
Investment Analysis spreadsheet. If you wish to simulate the accumulation of different investment properties over time,
you should use the Create Portfolio (Investor Menu) (see 7.4) to collate them. The data (number of properties, rents, loan
payments, etc.) from an existing portfolio will be added to the Wealth Builder but unlike additional properties, can only be
changed by recreating the portfolio.
It is recommended that you read Chapter 12 in Jan Somers More Wealth from Residential Property, for a detailed
example of using the PIA Wealth Builder to build a portfolio of investment properties.
The three key indicators that help to answer the question "When can I buy another investment property?" are:
•
Cash savings
•
Loan value ratio (LVR)
•
Debt service ratio (DSR)
Cash savings
Cash savings is simply the accumulated difference between total income and total expenditure and if positive, indicates
that the investors can afford the loan. This is simply an extension of the method used in the calculation of investment
capacity (see also 7.4) which refers to the amount of investment property that an investor (or a couple) can afford after
setting aside sufficient funds to cover their normal living expenses. In this spreadsheet cash savings are accumulated
from year to year and, naturally, total expenditure (which includes taxes, living expenses, rental expenses and any loan
payments) in any year should not exceed the monies available (i.e. accumulated savings up to that year plus total
salaries, rental and other income for that year).
Loan value ratio (LVR)
The LVR is used by banks as a measure of your loan security and is calculated as total monies borrowed, including the
new loan, divided by the total value of assets being used as security for the loans. As a general guide, the LVR should
definitely not exceed 100% and preferably, should not exceed 80% (loans with higher LVRs would likely attract mortgage
insurance).
Debt service ratio (DSR)
The DSR is used by banks as a measure of your ability to service the loan and is calculated as a function of total debt
repayments compared to total income, including rental income. The method that lending institutions use to calculate this
index varies considerably. Generally, only a percentage of income is deemed eligible (as a guide it is 30% of salary plus
about 80% of gross rents) and loan payments are often deemed higher than actual to allow for a potentially higher
interest rate during the course of repayments. The DSR is then simply the ratio of deemed loan payments to eligible
income. As a guide, the DSR should be below 100%. If the DSR is greater than 100%, then the loan is less likely to be
granted.
e.g. DSR =
Loan payments x 100%
30% wages + 80% Rent
PIA User Guide
Page 65
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
The Wealth Builder spreadsheet is divided into two areas:
Start of Year
End of Year
This chapter describes each row (and the associated dialogs) from top to bottom. For your orientation, the row being
described is shown with the row above and below:
If you have not made any changes, projections are based on default values.
PIA User Guide
Page 66
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.1
Start of year row
Wealth Builder
Start of Year
Home value
John Doe – for personal use only
Input
Projections over 10 years
2013
1yr
2yr
3yr
5yr
6.00% 480,000 508,800 539,328 605,989
10yr
810,950
The value in the input column of the spreadsheet shows the year from which PIA calculates future wealth building. The
year variable is common to all spreadsheets within PIA. The projections columns show the projected results for the years
selected. Note that all variables in the upper part of the spreadsheet refer to values at the start of the year whereas those
at the bottom refer to values at the end of the year. For details of setting projections, see 3.1.
5.2
Home value row
Start of Year
Home value
Home loan balance
2013
6.00%
17.7yr
1yr
480,000
336,000
2yr
508,800
326,989
3yr
539,328
317,230
5yr
605,989
295,215
10yr
810,950
222,057
The Home value row shows the projected value of the investor’s home.
The value in the input column reflects the average capital growth rate over the years shown in the Projection columns.
Changing this average rate by selecting and over-writing it will result in the growth rates for each year being set to this
same rate. The individual annual home values and/or growth rates may be altered in the Home Value Growth dialog (see
below). The future home values shown in the projections columns of the spreadsheet are calculated using these annual
capital growth rates.
Home Value Growth dialog
Clicking on the Home value row title (or double-clicking on the number in the input column) displays the Home Value
Growth dialog. This facility allows you specify the individual annual capital growth rates or the actual home valuations.
When one is changed, PIA calculates the others. The value of your home is used in calculating the loan value ratio.
Property value
This row shows the
projected property values
for up to 40 years. These
are based on the growth
rates shown in the next
column.
Growth rates
The value in the input
column in the spreadsheet
reflects the current capital
growth rate upon which the
future value of your home
is to be calculated. These
values may be altered in
the Growth rates row in the
Home Value Growth dialog.
PIA User Guide
Page 67
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.3
Home loan balance row
Home value
Home loan balance
Inv. property value
6.00%
17.7yr
6.00%
480,000
336,000
400,000
508,800
326,989
424,000
539,328
317,230
449,440
605,989
295,215
504,991
810,950
222,057
675,792
The value in the input column of the Home loan balance row shows the remaining term of the home loan. The rest of the
row shows the projected balance of the home loan over the projected years. In general this loan will be on the home only
but, in general, could be the consolidation of several non-deductible loans (see below).
Loan Consolidation dialog
Clicking on the Home loan balance row title (or on any value in the projection columns) opens the Loan Consolidation
dialog. This enables you to enter details of all your non-deductible loans, including that on your home (see 4.5).
5.4
Investment property value row
Home loan balance
Inv. property value
Total inv. properties
17.7yr
6.00%
336,000
400,000
1
326,989
424,000
1
317,230
449,440
1
295,215
504,991
1
222,057
675,792
1
The value in the input column of the Investment property value row title on the spreadsheet represents the anticipated
average annual capital growth rate of the investment property. If you select and overwrite this value, the growth rate for
all years is set to the new value.
The values in the projections columns show the anticipated increase in the property value over the projection period. PIA
calculates these using the specified capital growth rates.
Property Value dialog
Clicking on the Investment property value row title opens the Property Value dialog. This allows you to make adjustments
for renovations or the additional purchase of a furniture package. It also allows you to enter a different Market value if this
is not the same as the purchase price of the property (Book value) (see 3.2).
Investment Property Growth dialog
Clicking on a cell in the projections columns (or double-clicking the cell in the Input column) opens the Investment
Property Growth dialog, which allows you to specify growth rates year by year (see 3.7).
5.5
Total inv. properties row
Inv. property value
Total inv. properties
Total of all loans
6.00%
400,000
1
715,356
424,000
1
706,345
449,440
1
696,586
504,991
1
674,571
675,792
1
601,412
The values in the projections columns of the Total inv. properties row specify the cumulative number of investment
properties. If you already have existing investment properties that you have specified in creating a portfolio (see 7.4),
these properties will be included. When properties from a portfolio are included, the row title reads “Total inv. properties
(pf)”.
You may enter values directly into the projections columns however you will not be able to specify fewer properties than
shown in the previous year or fewer properties than defined in an existing portfolio.
You can also let PIA maximise the number of investment properties according to criteria that you set (see 7.4).
PIA User Guide
Page 68
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.6
Total of all loans row (start of year)
Total inv. properties
Total of all loans
Total of all values
1
715,356
880,000
1
706,345
932,800
1
696,586
988,768
1
674,571
1.111m
1
601,412
1.487m
The values in the projections columns of the Total of all loans row show the derivation of the total loan balance (including
any loan on your home and any loans on an existing property portfolio). In other words, for each projected year, the total
loan on all properties is the total loan on all investment properties (including any you may have specified in an existing
portfolio) plus the loan on your own home. If the “Show term” item is selected in the Preferences dialog, the first item in
the Total of all loans row will show the years until all loans are repaid. If this is greater than 40 years, the undefined
symbol (“?????”) will be displayed.
Total Loans dialog
Clicking on the Total of all loans row title or double-clicking on any of the values in the projections columns opens the
Total Loans dialog.
For the start of each year shown, this shows the cumulative number of investment properties included in the calculations,
the amount owing on the home loan, the total of investment loans, and the total loan balance. Also included are the home
and investment interest and payments. These figures are used to calculate the total loan balances for the ends of each
year which are shown broken down into home loan, investment loan components (from the property in the Investment
Analysis spreadsheet – see 3.10) and any existing portfolio loans.
Credit line
If you check the Use credit line to repay loans check box, PIA recalculates the loan payments and balances when all
surplus cash is used to help repay the loan principal. This is equivalent to clicking CL in the toolbar for this spreadsheet.
PIA User Guide
Page 69
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.7
Total of all values row (start of year)
Total of all loans
Total of all values
Loan value ratio
715,356
880,000
81%
706,345
932,800
76%
696,586
988,768
70%
674,571
1.111m
61%
601,412
1.487m
40%
The values in the projections columns of the Total of all values row show the predicted value of all properties – including
your home and any investment properties you have specified in an existing portfolio – in the selected years.
The total value of all properties is the total value of all investment properties, including any existing property portfolio,
plus the value of your home. The total value is important in calculating the total loan value ratio when all properties are
used as collateral for the loan.
Total Value dialog
Clicking on the Total of all values row title or on any of the values in the projections columns opens the Total Value
dialog. This summarises the total value of all investment properties together with value of your home in the selected
years.
PIA User Guide
Page 70
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.8
Loan value ratio row
Total of all values
Loan value ratio
Rental income
4.00%
880,000
81%
17,836
932,800
76%
18,549
988,768
70%
19,291
1.111m
61%
20,866
1.487m
40%
25,386
The values in the projections columns in the Loan value ratio row show the predicted loan value ratio (LVR) for the
selected years.
The LVR is simply the ratio (expressed as a percentage) of the total loans to the total value of properties used as security
for those loans. Naturally the lender would not want the LVR to exceed 100%, as it would not be possible to recover the
total loan amount if you were to default on the loan repayments. In fact, if the LVR exceeds a certain figure (usually
around 80%), the lender may insist on mortgage insurance, which the borrower has to pay for.
Loan value ratio dialog
Clicking on the Loan value ratio row title or on any of the values in the projections columns opens the Loan value ratio
dialog. This shows the derivation of the LVR for each year of the projection, taking into account the total value of
investments and your home as well as the amounts owing on existing loans.
PIA User Guide
Page 71
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.9
Rental income row
Loan value ratio
Rental income
Other income
4.00%
4.00%
81%
17,836
95,000
76%
18,549
98,800
70%
19,291
102,752
61%
20,866
111,137
40%
25,386
135,215
The value in the input column shows the anticipated average annual rate of increase in rental income for the investment
property from the point in time rents are indexed (by default this is from the first year – see 3.9). If you overwrite this value,
it resets the rate of increase to this value in all years (see 3.8).
The values in the projections columns show the anticipated total income from rent, including that from any existing
property portfolio.
Annual Rental Income dialog
Clicking on the Rental income row title or on any of the values in the projections columns opens the Annual Rental
Income dialog, allowing you to change the annual rent per property and set inflation indexing. For details, see 3.9.
PIA User Guide
Page 72
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.10 Other income row
Rental income
Other income
Total income
4.00%
4.00%
17,836
95,000
112,836
18,549
98,800
117,349
19,291
102,752
122,043
20,866
111,137
132,002
25,386
135,215
160,601
The Other income row shows your projected joint non-property net income over selected years. Rental income is
expressed directly in the Wealth Builder spreadsheet. Non-property net income is simply the non-property taxable income
plus any non-property non-taxable income. The non-property taxable income is calculated as the total non-property
income (e.g. salary and wages) less the total non-property deductions (e.g. work-related expenses).
The value in the input column shows the anticipated average annual rate of increase in this non-property net income from
the time that it is indexed (by default this is from the first year). You can overwrite this value if indexation is from the first
year.
The values in the projections columns show the anticipated income for the selected years based on the rate of increase
indicated in the input column.
Joint Net Income dialog
Clicking on the Joint net income row title opens the Joint Net Income (Non-Property) dialog. This shows the derivation of
the joint net income shown in the Wealth Builder spreadsheet.
Non-Property Income
Non-property income includes
salary/wages and any other income
excluding rents.
Non-Property Deductions
These include any work-related and
other deductions, but exclude
property-related deductions.
Non-Property Taxable Income
Your non-property taxable income is
total non-property income less total
non-property tax deductions.
Non-Property Net Income
Non-property net income is nonproperty taxable income plus any nontaxable income.
Non-taxable income
Non-taxable income for both investor
and partner can be entered on the
Non-taxable income row in the Current
Taxable Income dialog (see 7.4).
PIA User Guide
Page 73
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.11 Total income row
Other income
Total income
Rental expenses
4.00%
4.00%
95,000
112,836
5,621
98,800
117,349
5,846
102,752
122,043
6,080
111,137
132,002
6,576
135,215
160,601
8,001
The values in the projections columns in the Total income row show your projected total income for each year of the
projection, that is, Rental income (see 6.9) plus Joint net income (see 6.10).
5.12 Rental expenses row
Total income
Rental expenses
Living expenses
4.00%
4.00%
112,836
5,621
30,950
117,349
5,846
32,188
122,043
6,080
33,476
132,002
6,576
36,207
160,601
8,001
44,052
The Rental expenses row shows projected annual rental expenses.
The value in the input column shows the projected annual rate of increase for rental expenses from the point in time that
they are indexed (see 3.11).
The values in the projections columns show the projected annual rental expenses for each year of the projection.
Annual & Special Rental Expenses dialog
Clicking on the Rental expenses row title or on any of the values in the projections columns opens the Annual & Special
Rental Expenses dialog. This dialog allows the entry of any special expenses incurred from time to time that are best
treated separately from normal indexed rental expenses (see 3.11).
PIA User Guide
Page 74
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.13 Living expenses row
Rental expenses
Living expenses
New tax
4.00%
4.00%
5,621
30,950
5,940
5,846
32,188
6,859
6,080
33,476
8,807
6,576
36,207
12,370
8,001
44,052
22,421
The values in the projections columns of the Living expenses row are the predicted annual living expenses for the years
of the projection. These values are based on data entered in the Annual Living Expense Budget dialog.
Clicking on any of the values in the projections columns of the Living expenses row opens the Annual living expenses
dialog (see 4.11).
Annual Living
Expense Budget
dialog
Clicking on the
Living expenses row
title opens Annual
Living Expense
Budget dialog. Here
you can record all
monies that need to
be put aside for
household living
expenses. These –
together with any
home loan payments
– constitute the total
non-deductible
expenses and need
to be taken into
account in
calculating your
capacity to purchase
property.
Add, remove and
modify buttons
You can modify or
remove items or add new items by clicking on the Add,
Remove or Modify buttons or double-clicking the item itself.
(To remove or modify an item, select the item before clicking
the appropriate button.)
The Expense Item dialog opens, allowing you to enter yearly,
weekly, monthly and quarterly expenses.
Home loan button
Clicking on the Home Loan button in the Annual Living
Expenses Budget dialog enables you to make
alterations to the conditions governing repayment of the
home loan, that is, amount owing, interest rate, loan
payment rate. PIA then indicates the remaining term of
the loan. Any changes are reflected in the Home loan
PIA User Guide
Page 75
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
payments (annual) row of the Annual Living Expense Budget dialog.
Report button
Clicking on the Report button closes the dialog and generates a Living expenses report (see 7.2).
Annual Living Expenses dialog
Clicking on any of the values in the
projections columns of the Living expenses
row opens the Annual living expenses
dialog (see 4.11).
PIA User Guide
Page 76
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.14 New tax row
Living expenses
New tax
Loan payments
4.00%
30,950
5,940
65,914
32,188
6,859
65,914
33,476
8,807
65,914
36,207
12,370
65,914
44,052
22,421
65,914
The values in the projections columns in the New tax row show the projected total tax payable each year after current
taxable income is adjusted to new taxable income as a result of rental deductions (see 6.10).
Under structured tax scales, the harder you work, the more you earn, the more tax you pay. With negatively geared
investment property, the harder you work, the more you earn, the more tax you get back. This means that tax benefits
are maximised if the property is purchased in the name of the highest-income earner. Such details can be entered in the
Tax Benefits dialog.
New Tax Payments dialog
Clicking on the New tax row title opens the New Tax Payments dialog
This shows the
derivation of the new
tax that will need to
be paid.
PIA User Guide
Page 77
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.15 Loan payments row
New tax
Loan payments
Total expenditure
11,451
44,409
89,222
11,698
44,409
90,136
12,606
44,409
91,724
14,117
44,409
94,637
17,711
44,409
101,990
The values in the projections columns of the Loan payments row show the total amount of loan payments (home and
investment loans) for the selected years.
Loan Interest & Type dialog
Clicking on the row title or on any of the values in the projections columns opens the Loan Interest & Type dialog,
allowing you to specify the interest rates and type of investment loan (see 3.5).
To see the derivation of the total loan payments, click on the Total of all loans row of the spreadsheet to open the Total
Loans dialog.
5.16 Total expenditure row
Loan payments
Total expenditure
Cash savings
$40,000
65,914
108,425
4,411
65,914
110,807
10,954
65,914
114,277
18,721
65,914
121,067
38,909
65,914
140,387
119,989
This row simply totals all expenditures itemised in the rows above, that is, rental expenses, living expenses, loan
payments and indicates projected values for the selected years.
PIA User Guide
Page 78
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.17 Cash savings row
Total expenditure
Cash savings
Debt service ratio
$40,000
108,425
4,411
154%
110,807
10,954
148%
114,277
18,721
142%
121,067
38,909
132%
140,387
119,989
108%
The value in the input column in the Cash savings row shows the Initial savings recorded in the Accumulated Cash
Savings dialog described below. PIA assumes that you have sufficient cash savings to cover any initial deposits on
properties purchased at the start of year 1.
The values in the projections columns show the projected accumulated savings for each of the selected years taking into
account total income and expenditure. In other words, the Cash savings row shows the total monies available in an
account after all income and expenditure items have been taken into account. Thus the cash savings in any year are the
cash savings in the previous year less any initial outlays of new properties, plus the net income from all sources.
The initial cash savings available must (by definition) be sufficient to cover the initial outlay (specified in the Investment
Analysis spreadsheet) for all the investment properties purchased at the beginning. If the initial savings are set to zero, it
means that the initial outlay must also be zero and all costs borrowed.
If an initial outlay was made (deposit paid) for the initial investment property, PIA assumes that an outlay of the same
level (same percentage of the property value) applies to subsequent properties.
Accumulated Cash Savings dialog
Clicking on the Cash savings row title or on any of the values in the projections columns opens the Accumulated Cash
Savings dialog. This shows the total income and expenditure as well as net and accumulated savings for each selected
year of the projection. The accumulated savings figures are also shown in the projections columns of the Cash savings
row.
PIA User Guide
Page 79
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.18 Debt service ratio row
Cash savings
Debt service ratio
End of Year
$40,000
4,411
154%
10,954
148%
18,721
142%
38,909
132%
119,989
108%
The values in the projections columns of the Debt service ratio row show the projected debt service ratios (DSR) for the
selected years.
Financial institutions commonly assess an investor's ability to service a loan based on a comparison of income (including
that from the investment property) with total loan payments.
The debt service ratio (DSR) is a guide to loan eligibility. It is defined here as the ratio of deemed payments to eligible
income.
Eligible income is that part of gross income that a financial institution will accept as disposable. This could be 30% or
wages or salary, the remainder being required for living expenses, and may be 80% of rental income.
Deemed payments are the financial institution’s conservative estimate of future payments, allowing, for example, for an
increase in interest rates.
The total of these qualified incomings must be equal to or greater than total loan payments. In most cases, these loan
payments are adjusted for the risk of interest rate rises, so that a total of deemed outgoings is used.
As a guide, a DSR of less than 100% indicates that the loan payments are affordable.
Debt Service Ratio dialog
Clicking on the Debt service ratio row title or on any of the values in the projections columns opens the Debt service ratio
dialog.
Debt service ratio
The Debt service ratio area shows you the derivation of the DSR shown in the spreadsheet. It also allows you to enter
eligibility factors for various types of income. It also allows you to enter a risk adjustment factor to “load” outgoings.
(Example: a risk adjustment factor of 110% will add a loading of 10% to outgoings.) PIA then calculates the DSR
(deemed payments divided by eligible income).
Annual Debt service ratios
The Annual Debt service ratios
area shows annual DSRs over
projected years. Again,
deemed payments divided by
eligible income gives the debt
service ratio.
PIA User Guide
Page 80
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.19 End of year rows
Debt service ratio
End of Year
Total value
$480,000
154%
148%
142%
132%
108%
932,800
988,768
1.048m
1.178m
1.576m
The End of year rows show values for the end of the year, as opposed to the beginning of the year.
5.20 Total value row (end of year)
End of Year
Total value
Total loans
$480,000
$336,000
932,800
706,345
988,768
696,586
1.048m
686,017
1.178m
662,175
1.576m
582,944
The Total value row reflects the total end-of-year values of all investment properties (including any existing portfolio) plus
the value of your home. The end-of-year value is carried forward to the start of the next year and the value of any new
properties is added (see 6.7).
The value in the input column of the Total value row shows the current value of the investor’s home.
The values in the projections columns reflect the projected total values for all properties – principle residence and one or
more investment properties – for selected years.
The total value is important in calculating the total loan value ratio when all properties are used as collateral for the loan.
Total Value dialog
Clicking on the Total value row title or on any of the projections columns opens the Total Value dialog. See 6.7.
5.21 Total loans row (end of year)
Total value
Total loans
Net worth
$480,000
$336,000
$184,000
932,800
706,345
230,866
988,768
696,586
303,136
1.048m
686,017
380,798
1.178m
662,175
554,373
1.576m
582,944
1.113m
The Total loans row gives and overview of the total of all loans – on all investment properties plus the loan on your own
home.
The value in the input column in the Total loans row reflects the total loan balance for your home and any existing
property investment portfolio at the start of the initial year.
The values in the projections columns reflect the end-of-year total loan values for each of the selected years.
Total Loans dialog
Clicking on the Total loans row title or on any of the projections columns opens the Total Loans dialog (see 6.6). This
shows the derivation of the values in the projection columns and the start-of-year and end-of-year values.
PIA User Guide
Page 81
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.22 Net worth row
Total loans
Net worth
Investment equity
$336,000
$184,000
15.27%
706,345
230,866
44,644
696,586
303,136
70,084
686,017
380,798
97,051
662,175
554,373
155,935
582,944
1.113m
336,983
The value in the input column in the Net worth row is your net worth for the initial year. This includes the equity in your
own home.
The values in the projections columns reflect your projected net worth for the selected years.
Your net worth is the total value of all property assets, including the family home, less the amount of outstanding loans.
Net Worth dialog
Clicking on the Net worth row title or on any of the projections columns opens the Net Worth dialog. This shows the
derivation of the projected net worth from the total value, total loans and cash savings for each of the selected years. The
net worth for each of the years selected is also given in today’s $.
Today’s dollars:
Because of inflation, a future dollar will be worth less than a dollar today. In the row marked (In today's $): PIA shows the
value of the net worth in today's dollars, allowing direct comparison.
If you have specified that a credit line be used, any cash surplus shown in the spreadsheet is taken off the loan amount
before it is shown. Where no credit line is used, the cash surplus is simply added to the bottom line.
PIA User Guide
Page 82
PIA Version 7.320
Chapter 5
Wealth Builder spreadsheet
5.23 Investment equity row
Net worth
Investment equity
$184,000
15.27%
230,866
44,644
303,136
70,084
380,798
97,051
554,373
155,935
1.113m
336,983
The investment equity is the difference between the total value of investment properties and the debt remaining on them,
The value in the input column of the Investment equity row reflects the Internal Rate of Return for your investments over
the range of annual projections selected.
The values in the projections columns reflect the projected investment equity in each of the selected years.
Investment Equity dialog
Clicking on the Investment equity row title or on any of the projections columns opens the Investment Equity dialog. This
shows the relationship between property value, total value and total loan (and hence the equity) for the selected years. It
also shows the present value of the projected equity (that is, in today’s dollars) and the after-tax cash flow for each of the
years.
Note that if the property is sold – not recommended in the short-term – the full value of the equity will not be realised.
Selling costs (sales commission and solicitor's fees) and any capital gains tax must be deducted.
PIA User Guide
Page 83
PIA Version 7.320
Chapter 6
Linked Loans spreadsheet
This chapter provides an overview of the Linked Loans spreadsheet and its related dialogs. The term “linked loans” refers
to the fact that the spreadsheet displays details for both investment and home loans and examines the impact of links
between the two.
The main role of this spreadsheet is to provide a fast and easy way of analysing accelerated loan reduction,
especially for the home loan.
As a result of the different tax status of home and investment loans (in Australia and New Zealand, the interest on a
home loan is not tax-deductible whereas the interest on an investment loan can be claimed as a deduction against both
property and non-property income), there is a financial advantage in repaying a home loan before an investment loan.
This is the rationale behind the Linked Loans spreadsheet.
Repaying a loan faster will always involve making higher or more frequent repayments, or both. The Home Loan Analysis
spreadsheet already allows you to investigate ways of accelerating home loan reduction using such facilities as credit
lines or extra or fortnightly repayments.
There are three main scenarios:
Additional home loan repayments are made using surplus cash flows from an investment property
A credit line is used to accelerate the reduction of a home loan but investment loan repayments must also be
taken into account
A credit line is used to accelerate the reduction of both home and investment loans.
As these techniques impact on both investment and home loans, the variables displayed on the Linked loans
spreadsheet are a combination of those from the Investment Analysis spreadsheet – with details of your investment
property (see Chapter 3) – and the Home Loan Analysis spreadsheet – with details of your home loan (see Chapter 4).
The descriptions in this chapter assume that you are acquainted with these two spreadsheets.
Like the Home Loan Analysis spreadsheet, the key results from the Linked loans spreadsheet are the remaining time it
will take to repay the home loan and the total annual net cost of doing so, both of which are given at the bottom of the
spreadsheet and are recalculated automatically whenever a change is made to any of the relevant variables.
This chapter also describes the difference between independent and linked loans as well as the impact of capitalising
interest and capitalising expenses.
What are independent and linked loans?
By default the Linked Loans spreadsheet simply shows a summary of the variables from the Investment Analysis and
Home Loan Analysis spreadsheets with no interaction between the two sets of variables. In this case, Independent
loans appears at the top left of the spreadsheet, indicating that the loans are not yet linked in any way.
If you choose to link the loans as described below, Linked loans appears at the top left of the spreadsheet. Any surplus
cash flow from the investment property is now automatically channelled into home loan repayments until the home loan is
paid out, after which the normal home loan payments (called ex-home payments) are diverted to the investment loan until
it too is repaid. If the home loan is repaid before the investment property generates any surplus cash flow, no
continuation of ex-home loan payments is used to toward investment loan repayments.
When the Linked Loans setting is selected, a Linked loans report is available in the Reports menu. This report describes
all of the assumptions made with respect to variables and gives the “Current and Proposed” scenarios (that is with
independent and linked loans) for the home and investment loans.
Using and generating an investment surplus
Investment properties with positive cash flows (usually because the net rent exceeds the loan repayments) will have
surplus monies that can be used to help repay non-deductible loans such as the home loan.
With negatively geared investment property (negative gearing), cash flows are generally negative (that is, the loan
repayments exceed the net rent and tax credits). In this situation, there is not normally any investment surplus to use to
help pay down the home loan. However, an investment surplus can be generated, in theory at least, by simply not paying
the interest on the loan in the short term, that is by adding it to the loan. This is known as “capitalising the interest”. This
might even be done until the home loan is completely repaid. In theory, you can even capitalise expenses in a similar
way. These can be selected as items under the Settings Menu (see 7.7) and via icons on the toolbar (see 5.2).
Each time you link (or unlink) the loans or select (or deselect) capitalization of interest or expenses, PIA recalculates the
term of the home loan.
By default, PIA assumes that the capitalised component is not tax deductible. However, as it may – under some
circumstances – be deductible, there is a facility for switching interest to being wholly deductible. (See the Loan Interest &
PIA User Guide
Page 84
PIA Version 7.320
Chapter 6
Linked loans spreadsheet
Type dialog.) If the capitalised component is deductible (you will need to determine the current status for your specific
situation), there is a financial/tax advantage in effectively converting non-deductible debt (home loan) into deductible debt
(investment loan). Even if capitalised component is not tax deductible, this method may still be of value by acquiring an
investment property without any increase whatsoever in cash flow commitments.
Using the linked loans feature, the home loan is paid out faster whether or not your debt component is tax-deductible.
You may also have the added advantage of accumulating equity in your investment property while the total loan
repayments are limited to that of the original home loan.
Using a credit line
The use of a credit line for accelerated home loan reduction has already been described in Chapter 4, the Home Loan
Analysis spreadsheet. Credit line settings are independent for each spreadsheet. This means that if this option has been
selected in the Home Loan Analysis spreadsheet, it is not automatically selected in the Linked loans spreadsheet.
(The credit line toolbar icon will not be depressed.)
Nevertheless, if a credit line has been selected in the Home Loan Analysis spreadsheet, it will be used to calculate the
Term remaining at the bottom of the Linked loans spreadsheet.
If the investment and home loans not linked (Independent loans), the Term remaining in the Linked Loans spreadsheet
will be identical to that of the Home Loan Analysis spreadsheet.
Once the loans are linked (Linked loans), the cash flow surplus or cash flow commitments for the investment loan will
also be taken into account, net repayments will be set to those possible under the home loan analysis, and the Term
remaining will be adjusted accordingly. No changes are made to the investment loan repayment scheme unless there
surplus cash flows are available to assist in repaying the home loan. In the latter case, the ex-home loan payments will
be used to help repay the investment loan once the home loan is repaid.
If you choose to use a credit line from within the Linked loans spreadsheet, you are choosing a subtle change to the
model described above. In this case, the credit line is used to repay the home loan and – once it is repaid – automatically
reapplied to repayment of the investment loan.
Link Options dialog
The model has been made easier to follow and more flexible by providing a Link option dialog accessible under the
Settings menu.
PIA User Guide
Page 85
PIA Version 7.320
Chapter 6
Linked loans spreadsheet
6.1
Structure of the Linked Loans spreadsheet
As the above example shows, the rows of the Linked Loans spreadsheet are divided into two main areas:
Investment property
Home
You will recognize many of the rows – and their values – from the Investment Analysis spreadsheet (Chapter 3) and the
Home Loan Analysis spreadsheet (Chapter 4).
Where rows have already been described in Chapter 3 or 4, these descriptions are cross-referenced.
The remaining rows are described in detail.
PIA User Guide
Page 86
PIA Version 7.320
Chapter 6
Linked loans spreadsheet
6.2
Using the Linked Loans spreadsheet
You cannot effectively use the Independent and Linked loans spreadsheet until you have entered the details of your
investment property into the Investment Analysis spreadsheet and the details of your home into the Home Loan Analysis
spreadsheet. For full details of how to do this, please refer to Chapters 3 and 4.
The spreadsheet icons
You can use the toolbar icons to move quickly between the spreadsheets:
Displays the Investment Analysis spreadsheet.
Displays the Home Loan Analysis spreadsheet
Displays the Wealth Builder spreadsheet.
Displays a summary of the Investment Analysis and Home Loan Analysis spreadsheets:
Independent Loan spreadsheet (if you have not linked the loans)
Linked loans spreadsheet (if you have linked the loans).
The Link icon
The Link icon links (or unlinks) the investment loan and the home loan:
If loans are not linked (default), it links them.
If you have linked the loans, it unlinks them.
If you click this icon when in another spreadsheet, you are taken to the Linked loans spreadsheet and the loans are set to
Linked. When loans are linked, any investment surplus is added to the home loan repayments and the term of the home
loan is shortened accordingly.
The Link icon reflects the current status of the link:
Independent (default)
(not linked)
Linked
The spreadsheet title also reflects whether the loans are linked or independent, showing Independent loans or
Linked loans in the top left-hand corner of the spreadsheet.
Report
When loans have been linked, you can generate a detailed report outlining the interaction between the investment and
home loans by selecting Linked loans from the Report menu (see 7.2).
PIA User Guide
Page 87
PIA Version 7.320
Chapter 6
Linked loans spreadsheet
6.3
Investment property rows
The following rows in the investment property area of the Loans summary spreadsheet are identical to those in the
Investment Analysis spreadsheet. Please see Chapter 3 for details.
Year
Property value (here called Investment Property to distinguish it from the home)
Purchase costs
Investments
Investment loan
Gross rent /week
Interest
Rental expenses
Depreciation of building
Deprecation of fittings
Loan costs
Tax credit
After-tax cash flow
Of the investment property rows, only the Investment term & surplus row is new. This is described below.
PIA User Guide
Page 88
PIA Version 7.320
Chapter 6
Linked loans spreadsheet
6.4
Investment term and surplus
After-tax cash flow
Inv. term & surplus
Home value
-$40,000
?????
$480,000
-8,205
-8,205
508,800
-7,480
-7,480
539,328
-7,745
-7,745
571,688
-7,736
-7,736
642,348
-6,113
-6,113
859,607
The Investment term and surplus row shows the term remaining for the investment loan (in the input column) and any
investment surplus (in the projection columns).
Clicking on the Investment term and surplus row title or on any of the projections columns opens the Investment surplus
dialog.
Investment Surplus dialog
This dialog shows the
derivation of any investment
surplus and includes the
variables (rents, tax credits,
rental expenses, interest
payments, principal payments
and cash outlays) from which it
is calculated.
(For details of how to set
projections, see 3.1).
The implications for investment
surplus of having independent
or linked loans and capitalising
interest or expenses or both are
reviewed fully in 6.10.
6.5
Home value rows
The following rows in the investment property area of the Loans summary spreadsheet are identical to those in the Home
Loan Analysis spreadsheet. Please see Chapter 4 for details.
Home value row
Refinance costs row
Total repayments row
Total redraws row
The rows that are not identical to those in the Home Loan Analysis spreadsheet are described in the following:
Loan interest row
Loan balance row
Total LVR (loan value ratio) row
Term remaining
PIA User Guide
Page 89
PIA Version 7.320
Chapter 6
Linked loans spreadsheet
6.6
Loan interest row
Total redraws
Loan interest
Loan balance
8.00%
$336,000
0
26,554
326,989
0
25,806
317,230
0
24,996
306,661
0
23,169
282,819
0
17,097
203,588
The Loan interest row is a combination of the Interest rates (see 4.14) and Interest (4.15) rows in the Home Loan
Analysis spreadsheet.
The value in the input column shows the average annual interest rate over the term of the loan. If you select and
overwrite this value, the interest rates for all years are set to the new value.
The values in the projections columns show the projected total interest component for each year. PIA calculates these
values, so no entries can be made here.
Clicking anywhere in the Loan interest row opens the Interest Rates dialog (see 4.14).
PIA User Guide
Page 90
PIA Version 7.320
Chapter 6
Linked loans spreadsheet
6.7
Loan balance row
Loan interest
Loan balance
Total LVR
8.00%
$336,000
81.29%
26,554
326,989
25,806
24,996
23,169
317,230 306,661 282,819
Your total net cost per year
17,097
203,588
The figures in the Loan balance row reflect the amount owing at the end of each year after all loan additions and
repayments are accounted for. If the investment and home loans are linked, repayments may also include any investment
surplus from the investment property cash flows. If the home loan has been refinanced, the initial loan balance may also
include Refinance Costs (see 4.6).
Clicking on Loan Balance row title or on the value in the input column displays the Loan Consolidation dialog (4.5). This
facility allows you to consolidate and refinance all your non-deductible principal and interest loans into the one mortgage
package with your home loan.
Clicking on a value in the projection columns displays the Home Loan Balance dialog. This is the same dialog as
described in 4.16, except that it has an extra row: Investment surplus.
PIA User Guide
Page 91
PIA Version 7.320
Chapter 6
Linked loans spreadsheet
6.8
Total LVR (loan value ratio) row
Loan balance
Total LVR
Term remaining
$336,000
81.29%
326,989
317,230 306,661 282,819
Your total net cost per year
203,588
17.7yr
$43,770
$43,046
$41,679
$43,311
$43,301
The value in the input column shows the total to loan value ratio for the home loan and investment loan.
Clicking on the Total LVR row title displays the Total Loan Value Ratio dialog.
Total Loan Value Ratio dialog
This shows the projected annual combined loan to value ratio (LVR) for both your home and your investment property.
The total LVR is used by the bank to determine whether you qualify for the loan with or without mortgage insurance.
PIA User Guide
Page 92
PIA Version 7.320
Chapter 6
Linked loans spreadsheet
6.9
Term remaining
Total LVR
Term remaining
81.29%
17.7yr
$43,770
Your total net cost per year
$43,046 $43,311 $43,301
$41,679
The value in the input column of the Term remaining row shows the term remaining for the home loan.
This is the key value to watch when trying what-if scenarios with PIA, as reducing the term of the home loan is a key aim.
Clicking on the Term remaining row title or Input column displays the Loan Balance dialog (see 5.8). This allows you to
see how the loan balance for each year. You can also scroll through to see where it becomes zero.
6.10 Using the Capitalise interest and Capitalise
expenses options
As indicated at the beginning of the chapter, you can test a range of scenarios
using the Independent and Linked loans spreadsheets by submitting each to the
options capitalise interest and capitalise expenses.
Capitalise Interest and Capitalise Expenses are menu items in the Settings menu.
When switched ON, a tick (4) appears next to the item in the Settings menu.
When capitalization is selected, any investment surplus is added to the home loan
repayments and the term of the home loan is shortened accordingly. The total net
cost of both the investment and home loans remains at the level of the original
home loan repayment schedule.
6.11 Your total net cost per year
Total LVR
Term remaining
81.29%
17.7yr
$43,770
Your total net cost per year
$43,046 $43,311 $43,301
$41,679
This is literally the bottom line, the reason for all the entries and calculations in the spreadsheet: It shows the projected
total net cost per year resulting from the investment and the repayment of your home loan.
Total Annual Net Cost dialog
Clicking on a value in the
projection columns displays
the Total Annual Net Cost
dialog, showing the derivation
of the values for the set
projection.
PIA User Guide
Page 93
PIA Version 7.320
Chapter 7
Menus and toolbars
This chapter describes the functions of the pulldown menus and the toolbar at the top of the PIA screen.
The chapter reviews the menus first (from left to right), then the toolbar.
Please note that not all menu items are pertinent to all spreadsheets. Where individual items are not relevant and are
made unavailable, they will appear dimmed in the menu.
Many items will have been reviewed in earlier chapters. Where this is the case, a cross-reference is given.
PIA User Guide
Page 94
PIA Version 7.320
Chapter 7
Menus and toolbars
7.1
File menu
Clicking File in the menu bar opens the File menu.
The File menu contains commands for opening, saving and deleting files,
as well as for page and printer setup and printing.
There are additional items in the PIAPro version for saving and retrieving
property, finance and investor components (see description below).
New
Clicking New opens the Data Entry Check List dialog, which enables you to
enter data for a new property file (see 2.4). If you have specified not to
display the Data Entry Check List (see 2.4), the new file will open with the
investment analysis spreadsheet. The new file contains the values
specified in the default template (see 7.7).
Open
Clicking Open opens a dialog that allows you to open an existing document.
The Open dialog shows the default folder.
The default folder presented when you click to open a file is the folder you
last opened a file from or last saved a file to. The original default folder
(before you have saved any files) differs depending on the operating
system.
Save
If the current file has already been saved – indicated by a filename in the
title bar – clicking Save saves the current document. If no changes have
been made since it was saved, the Save menu item is dimmed.
If the file has not yet been saved – indicated by "Untitled – /." in the title
bar – clicking Save opens the Save As dialog, allowing you to enter a filename (and optionally select or create a folder to
save to).
It is recommended that you create a special folder to hold your PIA files. This can be done with the Save As dialog,
using the navigation features and the Create New Folder icon.
This becomes the default folder from which to open or save PIA files and makes it easier to locate and back up files.
Up One Level
PIA User Guide
Page 95
Create New Folder
PIA Version 7.320
Chapter 7
Menus and toolbars
Save As
Clicking Save As opens the Save As
dialog, allowing you to save an
opened document under a new
filename (and, optionally, in a different
folder). The folder in which the file is
saved becomes the default folder.
Save Report As
If a report screen is currently displayed
– after using one of the Report menu
commands (see 7.2) – clicking Save
Report As opens the Save Report As
dialog, allowing you to enter a
filename (and, optionally, select a
folder). The folder in which the file is
saved becomes the default folder.
If no report is currently displayed –
because, for example, you are in a
spreadsheet – Save Report As is
dimmed, as shown in the File menu
example at the beginning of this section.
Delete
Clicking Delete opens the Delete Files dialog, allowing you to
select files from the default folder for deletion and then delete
them by clicking the Delete button.
Revert
Clicking Revert replaces the current document with the previously saved version. The Revert command is only enabled
when changes have been made to the document. (If no changes have been made, it is dimmed.)
Load Image of Property
Clicking Load Image of Property displays the
Property Details dialog. This allows you to
load an image of the property as well as to
enter its address and description. If entered,
these items are saved with the files and
displayed in the relevant fields in any
generated reports.
Additional notes (confidential or otherwise)
can also be recorded and saved in the file.
These notes will not appear in any of the
reports.
PIA User Guide
Page 96
PIA Version 7.320
Chapter 7
Menus and toolbars
eMail File
Clicking Email File sends a message to your default email program to generate an email with the currently active PIA
document as an attachment. The recipient must have PIA software installed to read the file. Email File is only available
in the Professional Version of the software and, because it uses Window’s MAPI messaging system, only on the
Window’s operating system.
eMail Report
Clicking Email Report sends a message to your default email program to generate an email with the current report as an
attachment. Naturally, you must first generate a report within PIA before it can be emailed. This item is available only in
the Professional version of the software and, because it uses Window’s MAPI messaging system, only on the Window’s
operating system. The attached report is sent as a GOF file (Graphic Object Format) and can only be read by a recipient
with the PIA Report Reader software. You can download PIA Report Reader free from www.somersoft.com.au/pia.
Load Component PIAPro
Clicking Load Component opens the
Load Component dialog allowing you
to load component information that
you have previously saved to a
component file (see Save Component
below) into the current document.
To load a component, select
Properties, Finance or Investor from
the dropdown menu at the bottom of
the Save Component dialog, click on
the component you wish to load and
click on the Open button.
A component is a subset of a PIA
file that describes one of the three
major aspects required for investment
analysis:
- Property and its attributes
- Finance and its attributes
- Investor(s) and their attributes
Components are used in PIA as a way of mixing and matching different properties, different sources of finance or
different investors. When loaded, the data in a component replaces the corresponding information in the open document.
Save Component PIAPro
Clicking Save Component opens the
Save Component dialog allowing you
to specify a file name and file type for
the component you wish to save. A
component can be a property, a loan
or an investor. All parameters relating
to the chosen component are saved in
the file.
To save a component, select
Properties, Finance or Investor from
the dropdown menu at the bottom of
the Save Component dialog, enter the
filename (without a filename
extension) and click the Save button.
Components are automatically assigned the following file extensions:
Property component
PRP
Finance component
FIN
Investor component
INV
Components can be retrieved and loaded into any document at any time, replacing the corresponding information.
PIA User Guide
Page 97
PIA Version 7.320
Chapter 7
Menus and toolbars
Page setup
Clicking Page Setup opens the Printer Page Setup
dialog, allowing you to make adjustments to the page
setup options for printing.
Margins for the first and subsequent pages can be
specified in centimetres.
A header field can be selected for display on pages 2
onwards (e.g. property address, licensee name, client
name, etc).
Text displayed in colour on screen (e.g. red for
negative values) is printed black by default. Checking
the Print text in colour check box prints the text in the
same colour as shown on screen.
Print
Clicking Print opens the Print dialog, allowing you to enter specifications and properties for printing the selected
document. The Print dialog is operating-system and printer dependent, so its appearance and options vary.
Print Preview
Clicking Print Preview displays the selected document on the screen as it will appear when printed.
Click the Close button or press the ESC key to return to the previous PIA view.
Print Setup
Clicking Print Setup opens the Print Setup dialog, allowing you to select a printer, print properties, paper size, paper
source, orientation etc. The Print Setup dialog is operating-system dependent, so its appearance and options vary.
Exit
Clicking Exit causes PIA to save any changes you have made to preferences and to exit the application. If you have
made any changes to the current document since the last time it was saved, PIA displays a message asking if you wish
to save the changes. Click Yes to save the changes, No to exit without saving changes, and Cancel to return to PIA.
PIA User Guide
Page 98
PIA Version 7.320
Chapter 7
Menus and toolbars
7.2
Edit menu
Clicking Report in the menu bar opens the Report menu.
The Undo and Redo options allow you to undo changes (up to the last 5 changes made).
This is useful when changing values to examine What if? scenarios with the software.
If unavailable, the option is shown dimmed.
PIA User Guide
Page 99
PIA Version 7.320
Chapter 7
Menus and toolbars
7.3
Report menu
Clicking Report in the menu bar opens the Report menu.
PIA offers a number of report options. Reports can be viewed on screen, saved
as a new document using the Save Report As command in the File menu, or
printed using the Print command in the File menu.
Each of the report commands in the Report menu is described below. Not all
reports are available in all spreadsheets – reports that are not currently
available are dimmed.
The content of each report reflects the various options chosen for PIA.
A report is a different “view” of PIA data. It is not a separate window. For this
reason, to return to the spreadsheet you were previously viewing you must
select the appropriate spreadsheet from the toolbar or – the simplest way –
press the ESC key.
The Customised Report option – described below – allows you to combine
various report components in a single document.
Cash Flow Analysis report
Clicking Cash Flow Analysis generates and displays the seven-page Property Cash Flow Analysis report. This shows the
derivation of the after-tax cash flow for the selected property.
The first part of the report gives a tabulated breakdown of all the items that contribute to the cash flow in the first year of
the investment. The later parts of the report show the cash flow projections over time. .
Investment Analysis report
Clicking Investment Analysis generates and displays a detailed report of the cash flow analysis and anticipated internal
rate of return (IRR) for the current investment property.
The report begins with the summary report of the variables displayed on the spreadsheet, then gives a detailed
explanation and breakdown of all input variables displayed on the spreadsheet. This report also includes details of
projected internal rate of return (IRR), tax benefits and investment capacity. The report shows the projection period
selected in the Investment Analysis spreadsheet. (To change the projection period, see 3.1 Set Projections.)
Investment Analysis (Descriptive) report
Clicking Investment Analysis (Descriptive) generates and displays a full financial report of the cash flow analysis and
anticipated internal rate of return for current investment property. The report – several pages in length – contains a
breakdown of all of the input variables on the spreadsheet and shows how the cash flows are derived. The report shows
the projection period selected in the Investment Analysis spreadsheet. (To change the projection period, see 3.1 Set
Projections).
40 Year Projections report
Clicking 40 Year Projections generates and displays a report on the projected value of the selected property, your equity,
rent, total deductions, pre-tax cash flow, your tax credit and after-tax cash flow for each year over a forty-year period.
Values shown in red are negative.
Home Loan report
Clicking Home Loan generates a Home Loan Analysis report that comprises:
•
Details of the Home Loan Analysis spreadsheet's computer projections
•
Details of home loan payments and balance including property value, loan balance, equity, interest, loan payments,
loan redraws, and net cost over 30 years
•
Summary statement indicating loan repayment period and total net repayments including interest payments.
This command is only available from the Home Loan Analysis spreadsheet.
Wealth Builder Analysis
Clicking Wealth Builder Analysis generates a report showing the accumulation of investment properties specified in the
Wealth Builder spreadsheet. The report includes the spreadsheet, projections of property values, income and
expenditure, as well as graphics of Investment Equity & Net Worth, Loan to Value & Debt Service Ratios and After-Tax
Cash Flows.
Linked Loan Analysis report
Clicking Linked Loan Analysis generates a report describing the interest and repayment schedule for both the home and
investment loans. This is a detailed report that includes the projections from the Linked Loans spreadsheet, together with
detailed analysis of projected loan payments and balances, equity, and tax benefits.
This command is only available from the Linked Loan spreadsheet and when the loans have been linked (see Chapter 6).
PIA User Guide
Page 100
PIA Version 7.320
Chapter 7
Menus and toolbars
Capital Gains Tax report
Clicking Capital Gains Tax generates a Capital Gains Tax Calculation report. This report outlines CGT legislation and
shows how the capital gains tax liability (for Australia) would be calculated if the investment property were to be sold at
the end of the projection period selected in the Investment Analysis spreadsheet. (To change the projection period, see
3.1 Set Projections.)
Customised report
Clicking Customised Report opens the Customise Investment Report dialog.
This dialog allows you to generate a
customised report composed of various
report components, including graphics
pages.
The customised report options will be
retained for the next time you wish to use
this item..
Page header
This pull-down menu allows you to choose
the page header for your customised
report: The page header appears on the
second and subsequent pages of a report.
Page header text
This box is used to specify the text when
customising the page header.
Report heading
This box is used to specify the name when
customising the report heading.
Report sub-heading
This box allows you to enter text to be
displayed underneath the page header on
each page of the report.
Report options
The options are largely self-explanatory
and include the reports described above.
Clicking in the tickbox next to an item
selects it for the customised report.
The approximate number of pages is given in each case. Where reports are less than a full page, the report generator
paginates intelligently to save paper.
The customised report options are retained for the next time you wish to use this item
Graphics reports
You can include graphics in your customised report . For details of the graphics, see 7.4 Graphics menu.
We suggest you simply try generating various reports (on screen). You can then print out the ones you want.
PIA User Guide
Page 101
PIA Version 7.320
Chapter 7
Menus and toolbars
7.4
Graphics menu
Clicking Graphics in the menu bar opens the Graphics menu. Each item in the menu
generates a printable report, with a graphic depicting some aspect of the investment
analysis. The rest of each report shows the data on which the graphic is based.
Not all graphics are available in all spreadsheets. The menu items for graphics that are
not currently available are dimmed.
The thumbnails (miniature graphics) shown here are only intended to give you an idea of
what the graphics look like.
Who Pays?
Clicking Who Pays? generates and displays the Who Pays? graphics report. This shows a
pie chart of the relative contributions of the tenant, the taxman and yourself (the investor)
towards the cost of your property in the first year. Projections are also given for interest
costs, rental expenses, total costs, rental income, tax credit and your contribution over a 25year period.
The second page of the report includes a pie chart and bar graph showing the relative
contributions of the tenant, the taxman and yourself over the projected period.
Value & Debt
Clicking Value & Debt generates and displays the Investment Property Value and Debt
graphics report with a graph of projected value and debt over 25 years. The equity built
up in the property is simply the difference between the value and debt and is shown
clearly in the graphic.
A summary table lists the projected growth rate, property value, amount owing and equity
over 25 years. For an interest-only loan, the debt remains constant over time. For a
principal & interest loan, the debt declines over time as the principal is gradually repaid.
Cash Flows
Clicking Cash Flows generates and displays the Investment Property Cash Flows
graphics report with a graph of investment property cash flows over 25 years, contrasting
before- and after-tax cash flows.
A summary table shows projections over 25 years including projected rental income, loan
payments, rental expenses, pre-tax cash flow, tax credits and after-tax cash flows.
Rate of Return
Clicking Rate of Return generates and displays the Investment Property Internal Rate of
Return graphics report with a graph of projected equity in the property over 25 years for
the two scenarios: if the property is kept and if the property is sold..
A summary table details after-tax cash flow, equity and rate of return on the property for
two scenarios: if the property is kept and if the property is sold.
PIA User Guide
Page 102
PIA Version 7.320
Chapter 7
Menus and toolbars
Rental Yields
Clicking Rental Yields generates and displays the Capital growth & Net Yield report
with a graph showing rate of growth, net yield and net return (growth + net yield) over
the first 25 years of the investment.
The summary report shows the property value, growth rate, rental income, rental
expenses, gross yield and net yield over 25 years.
Bank Comparison
Clicking Bank Comparison displays the Bank Comparison
dialog. This allows you to specify an interest rate to generate a
report comparing the return from a bank deposit with the return
from your investment property.
Enter the desired interest rate and click OK. PIA generates and
displays a comparative graphic report showing projected returns
from equivalent investments in cash and property over 25 years.
The summary report shows after-tax cash flow, cash invested,
total cash and interest, property income, income and interest,
property equity and equity and income projections over 25 years.
Cash investments are regarded as equivalent to any negative after-tax cash flows (i.e. contributions by the investor) for
the property being analysed. Cash accumulated over time is calculated from the specified interest rate less any tax
liability, which is calculated at the investor’s marginal tax rate. The wealth accumulated in the property is the equity at
any point in time, plus any positive after-tax cash flows accumulated at the same interest rate and taxed at the same
marginal rate as for the cash investment.
The Cash Invested column of the table shows cash investments made in both the property and as investments in the
interest-bearing savings account (term deposit or similar).
Equity & Investment
Clicking Equity & Investment generates and displays the Equity & Investment graphics
report with a graph of investment property equity versus cumulative monies invested over
25 years. The cumulative monies invested is an accumulation of the negative and/or
positive after-tax cash flows for the investment (no time factor is taken into account). This
is compared with the equity built up in the property as a result of those cash flows.
A summary table shows projected total investment and equity over 25 years, detailing
after-tax cash flow, cumulative investment, property value, amount owing and equity.
Tax Liabilities
Clicking Tax Liabilities generates and displays the Cumulative Tax Liabilities graphics
report with a graph of cumulative tax liabilities over 25 years.
A summary table shows projected tax liabilities over 25 years detailing taxable income (in
single or joint name, as selected in the Tax Credits dialog, see 3.17), current tax liability,
cumulative tax, current tax, new tax liability, cumulative new tax and cumulative savings.
Weekly Averages
Clicking Weekly Averages generates and displays the Weekly Averages graphics report
with a graph of average weekly projected cash flow and average equity growth over 10
years.
.
PIA User Guide
Page 103
PIA Version 7.320
Chapter 7
Menus and toolbars
Investment Loan Payments
Clicking Investment Loan Payments generates and displays the Investment Loan
Payments graphics report with a gr aph and table of investment loan interest and
payments over 25 years.
If the investment loan is interest-only, the loan payment is the interest-only. If the
investment loan is principal & interest, the loan repayment remains constant but the
interest drops as the loan is gradually repaid.
Home Loan Payments
Clicking Home Loan Payments generates and displays the Home Loan Payments
graphics report with a graph and table of home loan interest and payments over 25 years.
Home & Investment Loan Balances
Clicking Home and Inv. Loan Balances generates and displays the Loan Balances
graphics report with a graph showing the home & investment loan balances over time (the
period usually for the remaining term of the home loan). A summary table shows interest
accrued, loan payments and loan balance for both home and investment property loans
over 25 years.
Combined Loan Value Ratios
Clicking Combined Loan Value Ratios generates and displays the Combined Loan Value
Ratios graphics report with a graph and table of the projected loan value ratio for
combined home and investment properties over a 25-year period.
Banks use the loan value ratio as a measure of security when the financing the
investment – the lower, the better. For an LVR above 80%, mortgage insurance m ay be
required, while an LVR above 100% will generally rule out any chance of finance. The LVR
is measured over all loans and properties under the one mortgage. Thus, equity in one
property (e.g. your home) can be leveraged to finance new property investments.
Home & Investment Equity
Clicking Home and Investment Equity generates and displays the Home and Investment Equity graphics report with a
graph of projected equity over time under two scenarios:
1) The red bars show the projected increase in equity in the home under the current repayment scheme.
2) The blue bars show the projected increase in total equity when an investment property is included.
A summary table shows the current and proposed scenario for home and investment properties showing loan payments
and equity build-up over 25 years.
This command is only available from the Linked Loans spreadsheet and only when loans have been linked (see
Chapter 6).
Wealth Builder
Clicking Wealth Builder generates and displays the Wealth Builder graphics report. This
provides a graph of total value vs. total debt over 25 y ears. A summary table details
property projections including number of properties, value, debt, equity, income,
expenditure and accumulated savings over 25 years.
This command is only available from the Wealth Builder spreadsheet.
PIA User Guide
Page 104
PIA Version 7.320
Chapter 7
Menus and toolbars
7.5
Investor menu
Clicking Investor in the menu bar opens the Investor menu.
This menu provides a number of options for entering investor-specific data
used in the spreadsheets and for generating investor-related reports.
Personal Details
Clicking Personal Details in the Investor menu opens the Investor’s Personal Details dialog, allowing you to enter investor
type and personal details. If entered, these items are displayed in reports.
The investor’s personal details and
any notes are stored in the property
file with the property and finance data.
Investor Type
For the most part, the investor will be
a person, but it is also possible to
analyse the cash flows on an
investment by another entity such as a
self-managed superannuation fund
(SMSF) or a company. For example,
if the entity is a SMSF, the entity’s
income would be that of contributions
by a person or persons, the tax scale
would be that for a superannuation
fund, CGT rules would be different,
there would be no “home” loan or
home repayments, nor would there be
any living expenses. Other options
that pertain specifically to a
superannuation fund are given in the
SMSF dialog (Settings menu).
Work-Related Income & Deductions
These fields allow you to enter
investor and partner income and
deductions. If this information has
been entered elsewhere – such as
via the Data Entry Check List (see 2.4),
the Tax Credits dialog (see 3.17), or
the Current Taxable Income Dialog
(see next page) – it is shown here.
Clicking the Notes button opens the Investor Notes dialog. You can use it for recording any additional notes you want to
save with the file. (This dialog can also be accessed by clicking Add Notes in the Investor menu.) (See next page/)
PIA User Guide
Page 105
PIA Version 7.320
Chapter 7
Menus and toolbars
Add Notes
Clicking the Add Notes menu item
opens the Investor Notes dialog
described above. The notes do not
appear in any of the generated
reports within PIA. The facility is
provided so that you can record any
additional information you wish
about the investor, property or
finance.
Current Taxable Income
Clicking Current Taxable Income opens
the Current Taxable Income dialog..
Current income and deductions are
divided into those relating to working
and those related to any existing
investment properties you may have.
To enter the latter you must create an
investment property portfolio by clicking
the Current Property Portfolio button
(see next page for details on creating a
property portfolio). Once this
information is extracted from the
portfolio files, the income and
expenses fields can then be edited if
needed.
Your taxable income is simply your
total income less total allowable tax
deductions, including any non-cash
deductions such as building and fittings
depreciation. Whereas your current
taxable income excludes non-cash
deductions, your net income includes
them as well as any tax rebates such
as a spouse rebate.
If the Investor type chosen is not a
person, the field names will reflect the
entity chosen.
PIA User Guide
Page 106
PIA Version 7.320
Chapter 7
Menus and toolbars
Current Investment Portfolio
Clicking Current Investment Portfolio opens the Current Investment Portfolio dialog. This dialog allows you to create a
new property portfolio or to view your current portfolio. If you create a new portfolio, the information is used to adjust your
taxable income in the Investment Analysis spreadsheet and the entire portfolio data is added to the Wealth Builder
spreadsheet. (For more on portfolios, see Why create a portfolio? on the next page.)
The property
investment portfolio
only covers
investment
properties. PIA
keeps the home
(principal place of
residence) separate
from investment
properties.
Select Current Portfolio Files dialog
Clicking the Create button opens
the Select Current Portfolio Files
dialog. To add a property to the
portfolio, click on a filename in the
Property Files column and then
click the Add button.
(Alternatively, you can double-click
the filename.) To add all the
properties, click the Add All
button.
The file name appears in the
Property Portfolio column on the
right.
You can click a file multiple times if
it is to be added to the portfolio
multiple times.
Remove
You can also remove files from the
Property Portfolio column in a
similar manner using the Remove
button or the Remove All button.
PIA User Guide
Page 107
PIA Version 7.320
Chapter 7
Menus and toolbars
Changing directory
Clicking the Directory button opens the Select Directory
dialog, allowing you to select a directory from which to
add files. All files making up a portfolio must reside in the
one directory.
Report
Clicking the Report button in the Current Investment
Portfolio dialog generates and displays a cash-flow
analysis report on the current investment portfolio.
To print the report, click the Print icon in the toolbar.
To return to the spreadsheet., press the ESC key or click
the appropriate spreadsheet icon in the toolbar.
Why create a portfolio?
PIA allows you to collate the property and finance data
from a specified set of property files (portfolio) to calculate
the combined cash flows and the rate of return for the
portfolio. You may want to create a portfolio for any
combination of the following reasons:
1) To account for the information (cash flows, asset
values and debts, tax deductions, etc) in the portfolio
when analysing the tax benefits of yet another
investment property (i.e. the one shown in the
Investment Analysis spreadsheet).
2) To take account of the information when planning the
accumulation of further investment properties (in the Wealth Builder spreadsheet)
3) To simply generate a cash flow report of the entire property portfolio (with or without the new property under review).
How it works: PIA extracts the relevant data from the property files, apportioning rents and rental expenses according to
whether the property is wholly or partly owned by the investor. The time frame for the portfolio cash flow projections is
determined by the settings in the current spreadsheet (as set in the Set Projections dialog, see 3.1) and uses the year of
purchase specified in each property file. During portfolio analysis, PIA ignores the data on investor's taxable income
stored in the individual property files as the analysis requires current taxable income (taken from the current
spreadsheet), excluding income and expenditure on investment properties.
Tax Benefits
Clicking Tax Benefits opens the Tax Benefits dialog (see 6.14).
Clicking the Report button generates a Tax Benefits report for the number of properties in the analysis, showing total tax
saving for investor, partner, and total. You can examine the impact on tax savings of having the title of the investment
property registered in single or joint names as well as assess the total tax savings for multiple investment properties.
This report can also be generated by clicking Tax Benefits in the Report menu.
Living Expenses
Clicking Living Expenses opens the Annual Living Expense Budget dialog (see 6.13). Clicking the Report button in the
dialog generates a summary report of annual living expenses. This report can also be generated by clicking Living
Expenses in the Report menu.
PIA User Guide
Page 108
PIA Version 7.320
Chapter 7
Menus and toolbars
Investment Capacity
Clicking Investment
Capacity opens the
Investment Capacity
dialog. This dialog
offers a facility for
determining a couple’s
investment capacity as
measured by how many
investment properties
like the one in the
current Investment
Analysis spreadsheet
they can afford.
The dialog shows the
cash flows in year 1 of
the investment, taking
home loan payments,
living expenses and tax
benefits into account.
To determine your
investment capacity,
increase the number of
investment properties
until the Net surplus
drops below zero. Then
decrease the number of
properties by one.
Clicking the Full
Report or Brief Report button generates an investment capacity report based on the number of properties entered under
Investment properties under Capacity in this dialog.
Using the Investment properties field in the Investment Capacity dialog allows you to analyse the number of identical
properties you could afford. For a cash -flow analysis report on a portfolio of different properties, first create a portfolio
and then choose the report option as described under Current Investment Portfolio above.
Maximise Wealth Builder
This menu item is only available if the Wealth
Builder spreadsheet is displayed.
Clicking on this menu item opens the Wealth Maximiser
dialog so that you can specify constraints for
accumulating investment properties in the Wealth
Builder spreadsheet. When you click OK, PIA calculates
the maximum number of properties you will be able to
accumulate each year over a 40-year period and
displays the results in the Wealth Builder spreadsheet.
PIA User Guide
Page 109
PIA Version 7.320
Chapter 7
Menus and toolbars
Tax Variation report
Clicking Tax variation generates and displays the Tax Variation Application dialog. The dialog helps you (the investor)
calculate your adjusted tax liability for the remainder of the financial year if you were to purchase the investment property
described in the Investment Analysis spreadsheet. Once you are happy with the figures in the dialog, you can generate a
Tax Variation report by clicking the Report button.
Financial Details
These include the date
from which adjustment
of tax instalments is to
take effect, the
investor’s annual
salary, and the
frequency of salary
payments. The
financial year is
determined by the
specified date of
adjustment, while the
starting date of the
financial year is
country-dependent (1
July in Australia, 1
April in New Zealand).
The current tax
instalments per pay
and the total tax
instalments paid up to
the date of adjustment
are calculated and
displayed.
Property Details These
include the date of
purchase, the
proportion of the
property that you (the
investor) own, as well
as the rental income
and rental deductions
(interest, expenses and non-cash deductions). If the date of purchase falls within the current financial year, PIA
calculates the fraction of the year remaining and apportions the annual rental income and rental deductions for the new
investment property accordingly. The defaults are derived from the current spreadsheet but you may wish to override
these. Current rental income and rental deductions refer to an existing investment property portfolio, if one has been
specified (see Current Investment Portfolio).
Assessable Income
This shows the derivation of total assessable income for the financial year.
Allowable Deductions
This shows the derivation of total allowable deductions for the financial year.
Projected Tax Liabilities
The new annual tax liability – which takes account of the investment property – is displayed here. From this, the
remaining tax liability for the year is calculated along with the corresponding adjusted tax instalment per pay period. The
flat-rate percentage is used by the Australian Tax Office to calculate adjusted tax instalments where regular payments
might vary.
Clicking the Report button produces a report entitled Guide to Variation of PAYG Income Tax Withholding.
In Australia, Section 15-15 of Schedule 1 of the Tax Administration Act 1953 provides that the commissioner may – to
meet the special circumstances of a particular case or class of cases – vary the amount an entity is required to withhold.
In other words, it is possible to vary your regular tax instalments in order to avoid an excessive end-of-year credit. This
used to be referred to as a variation of tax instalments for PAYE taxpayers under Section 221D but now is referred to by
the ATO as “Income Tax Withholding Variations” for PAYG taxpayers.
In New Zealand, people who receive wages or withholding payments may apply to Inland Revenue Department (IRD) for
an IR 23 certificate. It replaces the tax code declaration part of the IR 12 or IR 13. This certificate authorises the
employer to make PAYE deductions using a specified code, or deduct tax at a certain rate. For more details, see the
New Zealand Inland Revenue Department's website at http://www.ird.govt.nz.
Expenditure associated with negatively-geared rental property is a legitimate reason for variation of tax instalment
PIA User Guide
Page 110
PIA Version 7.320
Chapter 7
Menus and toolbars
deductions, qualifying under the category of Allowable Deductions. This facility calculates the appropriate variation in
regular tax instalments for an investor, taking into account the period over which the property is rented and the date on
which the adjustment is to be made.
PIA calculates the apportionment of rent and rental deductions based on the purchase date specified. If these figures are
overwritten, the next time you return to the dialog, PIA will ask whether you want the variables re-apportioned according
to the dates.
PIA User Guide
Page 111
PIA Version 7.320
Chapter 7
Menus and toolbars
Assets & Liabilities
Clicking Assets & Liabilities opens the Assets &
Liabilities dialog. The dialog acts as a template for a
net worth report that is in a format required by most
financial institutions when applying for an investment
loan. Lenders use this information to determine
whether you have sufficient collateral for the loan.
They do this by calculating a Loan Value Ratio (LVR).
However, not all assets may be used as collateral and
each item in the template has an associated LVR
checkbox for specifying whether it should be included
in the LVR calculation. For example, cars and boats
are assets but are not usually able to be used as
collateral for investment loans.
Income & Expenses
Clicking Income & Expenses opens the Income &
Expenses dialog. The dialog acts as a template for a
net cash flow report that is in a format required by
most financial institutions for investment loan
applications. Lenders use this information to
determine whether you have sufficient capacity to
service the loan. They do this by calculating a Debt
Service Ratio (DSR). However, not all income &
expense items are used in the calculations and those
that are may be used in different ways. For example,
a lender may consider 30% of wages and 80% of
rental income to be used in the calculations, but may
not consider welfare payments to be eligible at all.
Living expenses may or may not be used in DSR
calculations as most lenders have a pre-set formula
for estimating living expenses based on your family's
size and age. There are associated DSR buttons for
each item that enable you to define the appropriate
category (e.g. wages, rent, etc) and whether it is used
or not in the DSR calculation.
PIA User Guide
Page 112
PIA Version 7.320
Chapter 7
Menus and toolbars
7.6
Calculator menu
Clicking Calculator in the menu bar opens the Calculator menu.
The Calculator menu offers a range of options for calculating the projected value of
items that can then be entered into the spreadsheets and incorporated into reports.
The calculators function semi-independently of the spreadsheets in that they may take
default values from the spreadsheets (e.g. property price), but they do not return
changes in those variables to the spreadsheets.
When you make a change to any of the variables in a calculator, press the Enter key to
recalculate the answer(s).
All-purpose Calculator
Clicking All-purpose Calculator opens the Calculator. As part of the
Windows operating system, this calculator is only available in
Windows.
Capital Growth Calculator
Clicking Capital Growth opens the Capital Growth
Calculator. This enables you to calculate the average
annual rate of growth for a property over a number of
years, given its value at the beginning and end of the
period. This feature is useful in working out capital
growth in retrospect for any given property.
The calculator takes a default value for the initial year
from the Investment Analysis spreadsheet and applies a
default of number of years of 4. Once you have made a
change to one of the variables, press the Enter key to
recalculate the average annual growth.
PIA User Guide
Page 113
PIA Version 7.320
Chapter 7
Menus and toolbars
Inflation Calculator
Clicking Inflation opens the Inflation Calculator. This
enables you to calculate the average annual rate of
inflation for a cost item (such as rates) over a number of
years, given its value at the beginning and end of the
period.
The calculator takes a default value for the initial year
from the Investment Analysis spreadsheet and applies a
default of number of years of 4. Once you have made a
change to one of the variables, press the Enter key to
recalculate the average annual inflation rate.
Income Tax Calculator
Clicking Income Tax opens the Income Tax Calculator. This calculates
the income tax liability for a given taxable income using PIA's current
tax scale settings.
The initial value shown is the income entered for the Investor in the Tax
Credits dialog (see 3.17).
Stamp Duty calculator
Clicking Stamp Duty opens the Stamp Duty Calculator. This calculates the
state stamp duty owing on an investment property bought for a specified
price according to the currently selected stamp duty scale.
To select a different stamp duty scale or to modify the current scale, click
on Stamp Duties in the Settings menu to display the Stamp Duties dialog
(see 3.3).
PIA User Guide
Page 114
PIA Version 7.320
Chapter 7
Menus and toolbars
Sales Commission Calculator
Clicking Sales Commission opens the Sales Commission Calculator.
This calculates the “standard” sales commission for the sale of a
property for a specified value according to the currently selected sales
commission scale. To select a different sales commission scale or to
modify the current scale, click on Sales Commission in the Settings
menu to display the Sales Commission dialog (see 7.7). When first
opened, the Sales Commission Calculator shows the value of the
investment property in the right-most column of the Investment
Analysis spreadsheet.
In many places sales commission scales are deregulated and sales
commission is negotiable.
Capital Gains Tax Calculator
Clicking Capital Gains Tax opens the Capital Gains Tax Calculator. The menu item is disabled if you have previously
specified capital gains tax (CGT) to be inapplicable (e.g. as in New Zealand). See the Capital Gains Tax item under the
Settings menu in 7.7). Otherwise the form of the calculator reflects the CGT option chosen.
Capital Gains Tax Calculator (Discount Method)
This calculator shows how the
capital gains tax on a property is
calculated using the Australian
tax rules announced in
September 1999, which were
based on the recommendations
of the Ralph review of the tax
system. Under this regime,
indexation and averaging of the
earlier system are no longer
used. Instead, the tax liability is
based on a discount rate
(usually 50%) of the capital gain.
For capital gains tax purposes,
any furniture, fixtures and fittings
should be treated separately
from the property. They should
be subtracted from the purchase
price and their written down
value should be subtracted from
the sale price.
The Australian Government
legislated that after May 13
1997, building depreciation
claims should be written back
and subtracted from the cost
base.
PIA User Guide
Page 115
PIA Version 7.320
Chapter 7
Menus and toolbars
Land Tax
Clicking Land Tax opens the
Land Tax Calculator.
Land tax is an annual stateimposed tax based on land
ownership. The rules and tax
rates vary widely from state to
state and are subject to
change. For this reason it is
imperative that you consult the
relevant Office of State
Revenue for the latest rules
applying to the entity that owns
the land. You must then ensure
that the scales are defined
according to those rules (see
Land Tax Scale below).
In most states, the tax is based
on the aggregated value of all
land owned in that state and
the tax for an individual
property must be apportioned
accordingly. The principal place
of residence is usually exempt.
Neither the Northern Territory nor New Zealand impose the tax.
In PIA, the tax is treated as a rental expense. However, as it can't be automatically calculated from the
investment property alone, this calculator provides a tool for calculating it for your specific situation. You can
then enter the calculated tax into the Rental Expenses template.
The calculator is not linked directly with the Rental Expenses dialog. The tax payable must be entered
manually in one of the fields. (The field titles can be edited.)
Land Tax Scale
Clicking the Land Tax Scale button opens the Land Tax
Scale dialog, allowing you to view and edit the scale.
PIA User Guide
Page 116
PIA Version 7.320
Chapter 7
Menus and toolbars
Borrowing Capacity Calculator
Clicking Borrowing Capacity opens the Borrowing Capacity Calculator. This calculator enables you to estimate the
maximum amount you would be able to borrow to purchase investment property. This amount is assumed to be limited by
your Loan value ratio (LVR) and/or your Debt service ratio (DSR). The value of the investment property is also calculated.
The LVR is a measure of the loan security and is calculated as total monies borrowed, including the new loan, divided by
the total value of assets being used as security for the loans. As a general guide, the LVR should not exceed 100% and
preferably should not exceed 80% (loans with LVRs above 80% are likely to attract mortgage insurance).
The DSR is a measure of your ability to service the loan. It is calculated as a function of total debt repayments compared
to total income, including rental income. Only a certain percentage of income is deemed eligible by lending institutions (as
a guide it is 30% of salary plus about 80% of gross rents) and sometimes loan payments are deemed to be higher to
allow for a potentially higher interest rate during the course of repayments. Loan approval is unlikely for DSRs greater
than 100%.
You can set the maximum LVR and DSR to be used for calculation of Borrowing Capacity in the Borrowing Capacity area
of the dialog. If either (or both) of these values is limiting the borrowing capacity, it is (they are) shown in red. (In the
example below, Maximum DSR is the limiting factor, so 100% is shown in red). Clicking the calculated DSR or LVR field
displays a window showing how the value is calculated.
PIA User Guide
Page 117
PIA Version 7.320
Chapter 7
Menus and toolbars
Loan Payments Calculator
Clicking Loan Payments opens the Loan Payments
Calculator. This calculator enables you to select whether
the loan type is Principal & Interest or Interest Only. To
select either of these options click the appropriate radio
button in the Loan Type area.
You may also enter details of the loan amount, interests
rate, payments per year, term of loan etc.
Changing the periodic payment or payments per year
recalculates the term of the loan.
When calculating the term of the loan, switching from
monthly payments to fortnightly, it is assumed that the
loan payment is halved. Likewise, weekly is 1/4 of the
monthly payment, etc.
The term of an Interest Only loan is – by definition –
indefinite.
Loan Eligibility Calculator
Clicking Loan Eligibility opens the Loan Eligibility Calculator. This calculator provides a means of working out whether or
not you qualify for an investment loan under the criteria set down by a financial institution.
The calculator is divided into three areas. You can enter details of investor and partner income, eligible income, rental
income and other income. You can enter details of monthly and annual repayments of Loan "A" and Loan "B" (see 3.10),
credit cards and other expenses in the Outgoings area. The Debt Service Ratio is then reflected in the Loan Eligibility
area of the calculator.
It is general practice for the financier to consider a specified percentage of the investor's income (e.g. 30% of salary plus
80% of rent) as eligible income and then compare it with the total loan payment commitment. If the total outgoings are to
exceed the eligible incomings (i.e. the ratio of outgoings to incomings is greater than 100%) there is little likelihood of
receiving the proposed loan.
PIA User Guide
Page 118
PIA Version 7.320
Chapter 7
Menus and toolbars
Loan Refinance Calculator
Clicking Loan Refinance opens the Loan Refinance Calculator.
The calculator demonstrates
the impact on monthly
payments of refinancing.
Refinancing may also be
undertaken in conjunction
with the financing of an
investment property, in which
case the impact on the
monthly after-tax cost is also
shown.
Clicking the Current loans
button opens the Loan
Consolidation Calculator (see
4.5). This allows you to
specify a range of nondeductible loans that are to
be consolidated and
refinanced.
Clicking the Refinance costs
button opens the Home Loan
Refinance Costs dialog so
that the costs of refinancing
can also be included (See
4.6).
Retirement Goals Calculator
Clicking Retirement Goals opens the Retirement Goals Calculator.
When you enter details of your current
status with regard to investments and
liabilities, PIA calculates the number of
years needed to achieve retirement
goals (your target retirement income
expressed in today’s dollars), given
investment rates and after-tax rates of
return.
The Retirement Status area on the
Ready Reckoner shows current
investment 'nest egg', target retirement
income in today's dollars, the total 'nest
egg' required and the years needed to
achieve the goal.
The adjustment factor (nest egg divided
by income) is used to convert a target
retirement income to an equivalent total
asset value that will produce that
income. For investment property, the
factor of 20 assumes an effective net
yield of 5% for property whose value is
keeping pace with inflation.
PIA User Guide
Page 119
PIA Version 7.320
Chapter 7
Menus and toolbars
Superannuation Calculator
Clicking Superannuation opens the Superannuation Calculator.
This enables you to calculate the total amount accumulated in a superannuation fund, based on the parameters you enter
into the calculator. PIA then works out the total superannuation (both nominal and adjusted to today's dollars) that
accumulates over a specified period for the specified parameter values. All calculations are made monthly.
Your super contributions are measured as a percentage of your current salary. A percentage of these contributions is
lost in tax before they are invested. The total amount invested achieves earnings at the rate of return of return specified,
less the administration fees, which are expressed as a percentage of the total amount invested. The funds remaining
after taxation at the rate specified are reinvested.
Net contributions (i.e. net of contributions
tax) are assumed to be made at the start of
each month. Net earnings (i.e. net of
earnings tax) on the total balance are
added at the end of the month after which
administration fees are calculated and
subtracted from the balance.
PIA User Guide
Page 120
PIA Version 7.320
Chapter 7
Menus and toolbars
7.7
View menu
Clicking View in the menu bar opens the View menu.
Most of the commands in the View menu can also be accessed via the toolbar:
Data Entry Check List
Clicking Data Entry Check List on the View menu opens the Data Entry Check List dialog.
This can also by opened by clicking the Data Entry Check List icon. (For details of the Data Entry Check List, see
Chapter 2).
By default, PIA opens the Data Entry Check List every time you create a new file. You can change this setting in the
Preferences item under the Settings menu (see 7.7).
Investment Analysis
Clicking Investment Analysis in the View menu opens the Investment Analysis spreadsheet.
This can also be opened by clicking the Investment Analysis icon. The Investment Analysis spreadsheet is fully
described in Chapter 3.
Home Loan Analysis
Clicking Home Loan Analysis line in the View menu opens the Home Loan Analysis spreadsheet.
This can also be opened by clicking the Home Loan Analysis icon. The Home Loan Analysis spreadsheet is fully
described in Chapter 4.
Wealth Builder
Clicking Wealth Builder line in the View menu opens the Wealth Builder spreadsheet.
This can also be opened by clicking the Wealth Builder icon. The Wealth Builder spreadsheet is fully described in
Chapter 5.
Linked Loans
Clicking Linked Loans line in the View menu opens the Linked Loans spreadsheet.
This can also be opened by clicking the Linked Loans icon. The Linked Loan Analysis spreadsheet is fully
described in Chapter 6.
Toolbar
Clicking Toolbar on the View menu shows or hides the toolbar.
Status Bar
Clicking Status Bar on the View menu shows or hides the status bar.
PIA User Guide
Page 121
PIA Version 7.320
Chapter 7
Menus and toolbars
7.8
Settings menu
Clicking Settings in the menu bar opens the Settings menu. This
menu contains commands that allow you to set various options that
will be used by PIA
Credit Line
Clicking Credit Line in the Settings menu instructs PIA to set the loan model in the current spreadsheet to that of a credit
line (i.e. one in which repayments are maximised in accordance with the investor’s available disposable income). To do
this, PIA calculates the total income of both the investor and partner and their total committed expenditure (living
expenses, other mortgage payments and tax liabilities). Any surplus funds are used as additional repayments of the
principal. The Credit Line option can be selected independently in each spreadsheet. In those spreadsheets where there
is both a home loan and an investment loan (Linked Loans and Wealth Builder), repayments on the investment loan are
restricted to “interest only” until the home loan is completely repaid.
This option can also be selected by clicking the Credit Line icon on the toolbar (see 5.10).
Link Home & Investment Loans
Clicking Link Home and Investment in the Settings menu opens the Linked Loans spreadsheet (if it is not already
opened) and then links your investment loan and your home loan. When the loans are linked, any cash flow surplus from
the investment property is used to repay any debt on the home loan. This is fully described in Chapter 6.
This option can also be selected by clicking the Investment & Home Loans icon in the toolbar.
Link Options
Clicking Link Options in the Settings menu opens the Link Options dialog box. This is only enabled when the Linked Loan
Spreadsheet is displayed. The dialog provides options for specifying various forms that the linked Loan model can take
(e.g. by using all available cash to repay the home loan and then switching to use all available cash to repay the
investment loan). There are options for capitalising the investment loan and investment expenses while repaying the
home loan debt.
Capitalise Interest
Clicking Capitalise Interest in the Settings menu instructs PIA to change the nature of the investment loan so that interest
accruing is simply added to the loan amount rather than being met from the cash flows. This is reflected in the Interest
row in the spreadsheet: The row title changes to Interest (c). By default, PIA assumes that the interest on the capitalised
interest component is not tax deductible. See also Capitalize expenses below.
Capitalise Expenses
Clicking Capitalise Expenses in the Settings menu instructs PIA that – rather than being met from the cash flows – rental
expenses are simply added to the loan. This is reflected in the Rental expenses row in the spreadsheet: The row title
PIA User Guide
Page 122
PIA Version 7.320
Chapter 7
Menus and toolbars
changes to Rental expenses (c). By default, PIA assumes that any interest over and above the interest on the original
balance is not tax deductible (see Capitalise interest above).
Capital Gains Tax
Clicking Capital Gains Tax in the Settings menu
opens the Capital Gains Tax Settings dialog,
allowing you to select the method used to
calculate capital gains tax (CGT).
Australian capital gains tax (CGT) rules have
changed in recent years as a result of the
Ralph Review of Taxation: tax is now based on
a discount method. The discount rate for most
people is 50%, but this may vary for
companies.
Since May 13, 1997, the CGT rules have meant
that any building depreciation (capital
allowance) claims must be written back
(subtracted from the cost base) when
calculating the CGT.
Where CGT is not applicable (for example, in
New Zealand), select the CGT not applicable
radio button.
Stamp Duties
Clicking Stamp Duties in the Settings menu opens the Specify Stamp Duty Scale dialog (see 3.3).
Sales Commission
Clicking Sales Commission in the Settings
menu opens the Specify Sales Commission
Scale dialog, allowing you to specify the sales
commission scales used by PIA.
The default setting is the state (or country)
you chose for Stamp Duty scales in the
Stamp Duty Scale dialog (see 3.3).
If you make a change to the sales
commission scales, these are saved to the
PIA settings file.
Set Country Defaults
Clicking Set Country Defaults opens the Set Country Defaults dialog, allowing you to set program defaults that are
country-specific (see 2.3).
PIA User Guide
Page 123
PIA Version 7.320
Chapter 7
Menus and toolbars
Tax Scales
Clicking Tax Scales in the Settings menu
opens the Specify Tax Scale dialog. This
dialog allows you to reset the tax scales used
in PIA.
The default setting is the country you chose
in the Set Country Defaults dialog.
If you make a change to the tax scales, these
are saved to the PIA settings file.
There is an option for quarantining any tax
losses to the investment. i.e. they cannot be
offset against other income such as salary.
Selling Costs
Clicking on Selling Costs opens the Selling Costs dialog.
This dialog allows you to enter values for PIA to use
when calculating the cost of selling a property. These
include sales commission, solicitor's fees and - in
Australia – Capital Gains Tax.
The sale of properties in New South Wales also attracts
stamp duty on sale.
PIA User Guide
Page 124
PIA Version 7.320
Chapter 7
Menus and toolbars
NRAS Options
Clicking NRAS Options opens the NRAS Options
dialog. The dialog provides access to variables
that define the subsidies and rental discounts
associated with the Australian Governments
National Rental Affordability Scheme (NRAS).
See http://www.fahcsia.gov.au for details.
Clicking the NRAS Tax Incentive button opens
the NRAS Tax Incentive dialog (see below). For
easy comparison with an equivalent non-NRAS
property, the rental income and expense
parameters are set out side by side in two
columns. The NRAS parameters do not come into
effect until the Apply NRAS checkbox has been
clicked.
Clicking the Cash Flow Comparison button will
generate a simple first-year cash flow analysis
report for the same property under NRAS and
non-NRAS parameters,
NRAS Tax Incentive dialog
The dialog allows the specification of the first-year NRAS tax
incentive. This incentive is assumed to be indexed each year in
line with changes in the market rents.
It is also possible to specify whether or not total rental deductions
are to be adjusted for the proportion of income that is deemed
non-assessable non-exempt (NANE). The State component of the
tax incentive (25% of total tax incentive) is deemed NANE income.
PIA User Guide
Page 125
PIA Version 7.320
Chapter 7
Menus and toolbars
SMSF Options
Clicking SMSF Options
brings up the SelfManaged Super Fund
Options dialog.
Purchasing property in
the name of a selfmanaged super fund
(SMSF) is similar in
principle to purchasing
in the name of a single
investor where the
investor is the SMSF.
Naturally, many of the
parameter values will be
different. The SMSF
becomes the investing
entity and tax scales and
tax rules must be those
pertaining to
superannuation funds.
The income for the entity will be the regular superannuation contributions being made by one or more investors.
If you choose to apply the SMSF settings, then the following changes to the model take place:
1. Ownership is in a single name (i.e. the SMSF)
2. The annual income (one entity only) and expenses will be as specified in the dialog
3. The tax scale will be that pertaining to superannuation funds (see Settings menu/Tax Scales).
4. The CGT discount will be changed to that as specified in the dialog
5. Living expenses, home value, loan and repayments are not applicable and are set to zero.
If you also choose to apply a Pension Phase for the SMSF, then from the year you specify, there will be no tax on the
fund's earnings and no capital gains tax on sale. There may well be changes to the SMSF tax laws and you would be well
advised to check with the Tax Office. A proposed change at the time of publication was to introduce a tax (15%) on
earnings over $100,000.
When dealing with superannuation funds, it is often useful to monitor the total cash flow into and out of the fund, including
that of the investor contributions. This Annual Cash Surplus can be displayed at the bottom of the Investment Analysis
spreadsheet by clicking the corresponding checkbox in this dialog.
These settings will not be involked until you click the Apply SMSF Settings checkbox and OK.
Set Country Defaults
Clicking Set Country Defaults allows you to switch between defaults for Australia and New Zealand.
Save Default Template
Clicking Save Default Template saves the current spreadsheet as the default template. To revert to PIA’s default settings,
click Delete Default Template (see below).
PIA always opens a new property file with a set of default values. You can modify these values at any time to create your
own personalised start-up template so that you do not have to modify all of the variables every time you create a new file.
This is especially useful for values that are specific to you and do not change very much, such as your home loan details
and your own living expense budget, as well as your estimates for inflation, growth and interest rates.
You can set new default values at any time by simply making the changes and selecting the Set Default Template menu
item again.
Delete Default Template
Clicking Delete Default Template deletes the current template and startup settings and resets the PIA default values to
the “factory” settings. Exit and restart PIA to ensure that all changes take effect.
Consultant PIAPro
Clicking Consultant in the Settings menu opens the
Specify Consultant dialog. This allows you to
customise the consultant field in any generated
reports.
If you are the only consultant using this copy of the
PIA software, you might save the default template
once you have completed the field.
PIA User Guide
Page 126
PIA Version 7.320
Chapter 7
Menus and toolbars
Disclaimer PIAPro
PIAPro has a standard disclaimer which cannot be
edited. You can, however, append text to the
standard disclaimer.
Clicking Disclaimer in the Settings menu opens
the Disclaimer dialog. This allows you to make
additions to the standard disclaimer used in
reports generated by the Professional version of
PIA. You can add additional phrases, and change
the style and format of the disclaimer. (Changes to
the format affect the entire disclaimer, that is, the
standard disclaimer and any additions you make.)
Font Style, Alignment and Margins
You can set the font style, alignment of text, and
the width of the left and right margins to allow
space for a company logo. The entries in the Left
and Right boxes determine how far from the left of
the page the margins will start.
When you click on OK, a window is displayed
showing the disclaimer as it will be printed. The edited disclaimer is stored in the settings file and appears on all
subsequent documents printed.
Press the ESC key to return to the spreadsheet.
(This example shows the format set to italic, left-justified text.)
PIA User Guide
Page 127
PIA Version 7.320
Chapter 7
Menus and toolbars
Logo PIAPro
Clicking Logo in the Settings menu opens the
Logo dialog. This allows you to load a jpeg
image of your company logo to be displayed
in any generated reports. The exact size and
position of the logo can be fine-tuned in the
Logo Dimensions area.
Set Font
Clicking Set Font in the Settings
menu opens the Font dialog. This
allows you to customise fonts used
in spreadsheets and reports.
The default font is Times New
Roman Regular, 10 point. The
spreadsheets and reports are
optimised to display with a font size
of Times Roman 11 point.
If you exceed this size, some fields
may overlap and pagination may not
be optimal.
If you wish to enlarge text to make it
easier to read on screen, it is better
to use the Magnify command
described later in this section.
PIA User Guide
Page 128
PIA Version 7.320
Chapter 7
Menus and toolbars
Preferences
Clicking Preferences in the Settings menu opens the Preferences dialog.
Consisting of four pages called “tabs”, this dialog enables you to enter your preferences regarding the Investment
Analysis spreadsheet "Bottom Line", directories in which you wish to store files, as well as your choice of magnification
and colours. The options are described below.
Display tab
The top part of the Display tab contains the Investment Spreadsheet "Bottom Line" preferences area. This area contains
eight radio buttons that allow you to select how you would like the "bottom line" of the Investment Analysis spreadsheet to
be presented:
Click
To display
Pre-tax equivalent
Internal Rate of Return (IRR) and Pre-tax Equivalent of IRR
Net present value
IRR and Net Present Value
Real rate of return
IRR and Real IRR (i.e. IRR adjusted for inflation)
Cash on cash return
IRR and Cash on cash return
Rate of return (if sold)
IRR and IRR which takes account of selling costs
“Your cost/(income) per week"
Weekly costs with income in brackets
"Your income/ (cost) per week"
Weekly income with costs in brackets
“Weekly after-tax cash flow”
“Monthly after-tax cash flow”
“Annual cash surplus”
After-tax cash flow per week.
After-tax cash flow per month.
Annual cash surplus
In a typical negatively geared investment,
the after-tax cash flow is usually negative in
the early years and in this situation, you
would normally specify the convention to be
showing the “Cost/(Income) per Week” with
a negative cash flow shown as a positive
cost. On the other hand, if the properties
you analyse are more likely to be cash-flow
positive , you might prefer to show income
to be positive and any negative cash flows
in brackets i.e. “Income/(Cost) per Week”
For an entity such as an SMSF, it is often
useful to display the Annual cash surplus to
monitor whether there is sufficient fund
contributions being made by the investor(s)
to cover any negative gearing losses on the
investment property.
Years
The Notional years and Actual years radio
buttons allow you to select how PIA
displays years in spreadsheets and reports.
Undefined-value string
Sometimes entries into PIA will result in
values that cannot be represented in
numbers. For example, if the cash flows on
an investment property are positive from the
start, it is not possible to calculate a return
on investment as there is no investment (the return is infinite). In such situations, PIA represents the value as “?????” by
default. You can change this string if you wish.
Skip check list
By default, the Data Entry Check List (see 2.4) is displayed when you create a new property file – the structured format
ensures ease of entry of key parameters for new users.
However, experienced users may wish to bypass the Data Entry Check List and go straight to the investment analysis
spreadsheet when creating a new file. Here, values are automatically updated whenever changes are made. If you check
the Skip check list check box, PIA does not display a Data Entry Check List when you create a new PIA file.
You can still display the Data Entry Check List by clicking Data Entry Check List in the View menu or clicking the
icon in the toolbar.
PIA User Guide
Page 129
PIA Version 7.320
Chapter 7
Menus and toolbars
Disable row highlighting
If the Disable row highlighting check box is checked, spreadsheet row titles (on the left) no longer change colour and the
mouse cursor does not change shape when the mouse pointer passes over them. This option has been made available
because of possible conflicts in some operating systems.
Wealth Builder display option
If the Show loan term checkbox is ticked, the Wealth Builder spreadsheet will calculate the years until all loans are repaid
and display it in the input column of the Total all loans row. If the number of years exceeds 40, the figure will display as
undefined (“?????”).
File Locations tab
Clicking the File Locations tab displays the currently specified folders for storage of files. Clicking the Modify button
displays the Select Directory dialog, allowing you to select the directory where files will be stored. (See also 7.1).
If you use the Save As command or Open
command to save or open a file in a
directory other than the default directory,
PIA stores this as the default directory. This
information is updated whenever you open
or save a file, but can also be specified
directly using this dialog.
Magnification tab
Clicking the Magnification tab in the
Preferences dialog opens the Magnification
page.
You may enter the required magnification
directly in to the Specify percentage line.
Clicking the Default button resets the
magnification to the default calculated by
PIA based on the resolution of your graphics
card. This default setting ensures that the
screen is always readable when the
software is first installed.
You can magnify the view of the
spreadsheets and reports without affecting
printed output. The standard view is 100%
magnification. A magnification of 150%
results in a view that is 50% larger than the
standard while a view of 50% results in a
view 50% smaller than the standard.
PIA User Guide
Page 130
PIA Version 7.320
Chapter 7
Menus and toolbars
Colours tab
Clicking the Colours tab opens the Colours
page in which you can specify custom
colours for various parts of the user
interface. Clicking on the various buttons in
the page opens the operating system’s
Colour selection dialog (see below). After
selecting a colour, the colour of the button is
updated accordingly. Clicking the Defaults
button returns the colours to the PIA default
settings.
Background Colours refer to those of the
spreadsheets. Two of the spreadsheets
(Linked Loans and Wealth Builder) can be
divided logically into an upper and lower
part for which different colours can be
chosen to make interpretation easier.
Text Cell Colours refer to the foreground
colour of text in the various spreadsheet
cells. Active cells are those that can be
selected and overwritten. Inactive cells are
those that can’t be overwritten directly.
Negative cells contain negative values and
may be either active or inactive. By default,
spreadsheet cells that can be selected and
changed (active cells) are coloured blue.
Other cells (inactive cells) are coloured
black, except where their value is negative
in which case they are red.
Graphics colours refer to the different components in graphics reports (see 7.3). These are denoted by the You,
Tenant and Taxman buttons, the components for which appear in the Who Pays? graphic report.
Currency Tab
This tab provides options for using currency symbols other than the “$”. It
has been provided mainly for British or European users of the software.
PIA User Guide
Page 131
PIA Version 7.320
Chapter 7
Menus and toolbars
7.9
Help menu
Clicking Help in the menu bar displays the Help menu.
The Help menu gives you access to a variety of help options. It also includes registration,
feedback and on-line support options.
Help Topics
Clicking Help Topics opens the PIA Help Topics facility. Help can be obtained by using either the Contents, Index and
Search tabs.
The Contents tab displays the contents of PIA Help.
The Index tab displays an index of PIA Help.
The Search tab presents a search window, allowing you to search on keywords to find the help you are looking for.
Instructions on how to use the Help Topics are clearly given under each of the options.
Help Topics can also be accessed through the Help icon on the toolbar.
PIA User Guide
Page 132
PIA Version 7.320
Chapter 7
Menus and toolbars
Register
Clicking Register opens the Registration
Number dialog. PIA must be registered
if it is to run in normal mode.
If PIA is not registered, it runs in
demonstration mode in which many of
the key variables are locked.
The Registration Number dialog
appears when you run the software the
first time.
However, this item makes it possible to
register a copy of the software you have
been running in demonstration mode or
to register with a different name and
corresponding registration number.
Feedback
Clicking Feedback opens the Feedback dialog. This facility allows you to email valuable feedback on the software to
technical support.
Please use the Feedback option – it helps us tailor PIA to your needs.
Because of operating system restrictions, the Feedback feature is only available in Windows where MAPI support is
available.
PIA User Guide
Page 133
PIA Version 7.320
Chapter 7
Menus and toolbars
Support Online
Clicking Support Online sends a message to your MAPI -compliant email program to generate an email to
support@somersoft.com.au with the current document as a file attachment. Please specify the nature of your support
request in the body of the email before sending the email.
Because of operating system restrictions, the Support Online feature is only available in Windows where MAPI
support is available.
About PIA
Clicking About PIA opens the About PIA dialog. It shows copyright information, the address of Somerset Financial
Services, the website URL and the email address for support.
The Registration field (Reg:) at the bottom
of the window shows the first 4 characters
of the registration code and the
corresponding month-year when it was first
registered.
You can also open this box by
clicking the About PIA icon on the
toolbar.
PIA User Guide
Page 134
PIA Version 7.320
Appendix A – Glossary
After-tax cash flow (Investment Analysis spreadsheet)
These are all of the monies that flow into or out of your pocket after tax is taken into account.
Allowable deductions (Current Taxable Income dialog)
Allowable deductions consist of those expenses that relate to your work-related income and those expenses and
depreciation claims that relate to your rental income from your investment properties.
Amount owing (Home Loan Analysis spreadsheet)
The amount still owing on your home loan or a consolidation of non-investment loans.
Assessable income (Current Taxable Income dialog)
Assessable income includes salary/wages, rental income from any existing investment properties and any other income.
Book value (Property Value dialog)
The book value represents price paid for a property plus the cost of any initial renovations. In contrast, the market value
is what you might expect to get for it if you were to sell it.
Capital allowance (Depreciation of Building dialog)
This is another term used for the depreciation claim on the building.
Capital cost
Investment in the asset rather than expenditure on operating (or revenue) costs.
Capital Gains Tax
A tax on the net profit between purchase and sale.
Capital growth rate (Investment Analysis spreadsheet)
The average annual compound rate of growth in the property value.
Cash flow
The monies that flow in and out of your pocket.
Cash-neutral investment
The initial deposit required to make the after-tax cash flow in the first year equal to zero. In the Property Details area of
the Investment Analysis spreadsheet, this variable is shown as $0 if the investment is already cash positive when there is
no deposit.
Cash savings (Wealth Builder spreadsheet)
Cash savings represent the accumulated monies available after all income and expenditure is taken into account.
Component (PIAPro File menu)
A component is a subset of a PIA file that describes one of the three major sets of attributes required for investment
analysis: (a) those that describe the property, (b) those that describe the finance and (c) those that describe the
investor(s).
Consumer Price Index (CPI) (Investment Analysis spreadsheet)
An index of prices used as to measure of the rate of change in the cost of basic goods and services (i.e. the inflation
rate).
Cost base (Capital Gains Tax Calculator and reports)
Used in calculations for capital gains tax, cost base is the total purchase price of a property (including the costs of
acquisition, stamp duty, renovations, etc).
Credit line (various spreadsheets)
A type of loan account into which all income is paid and from which all expenses are redrawn. Used to accelerate
mortgage reduction.
Current net income (Current Taxable Income dialog)
Your current net income is simply the total of all your income (salary/wages, rental income from any investment
properties that you currently own and income from any other source, including any tax rebates) less your total cash
expenses.
Current taxable income (Current Taxable Income dialog)
Your current taxable income is simply the total of all your income (salary/wages, rental income from any investment
properties that you currently own and income from any other source) less your total allowable tax deductions (cash
expenses and depreciation claims).
Debt service ratio (DSR) (Wealth Builder spreadsheet)
The debt service ratio provides a guide as to the level of affordability of a loan. As it is defined within PIA, the value
should be below 100%. The methods that lending institutions use to calculate this index vary considerably. Most lending
institutions are conservative in calculating an investor’s affordability, such as ratio of the total loan payments to the sum of
30% of income and 80% of rent. DSRs greater than 100% are unlikely to gain loan approval.
PIA User Guide
Page 135
PIA Version 7.320
Appendix A
Glossary
Deemed payments (Debt Service Ratio dialog)
Deemed payments are the financial institution’s conservative estimate of future loan commitments, allowing, for example,
for an increase in interest rates.
Default folder
The default folder in PIA is the folder in which you save or retrieve PIA files. If you choose to save or retrieve files in a
different folder, that folder will then become the default folder.
Default template
PIA loads a template of default values into any new spreadsheet. You can specify your own template of default values by
using the Save Default Template menu item in the Settings menu or by checking the Use these values as defaults check
box in the Data Entry Check List (see 2.4). You can reset the "factory" settings with the Delete Default Template
command in the Settings menu (see 7.6.)
Defined term
This entry is for illustration only: The hyperlinked term is shown on page 7 and links to this definition.
Demonstration version
An unlicensed version of the software for evaluation purposes, the demo version is of limited practical use as a number of
key variables (such as property value, purchase costs and rental expenses) are locked and cannot be changed by the
user. A demonstration version can be converted into a licensed version by entering the licensee name and registration
code.
Depreciation of Fittings (Chattels) (Investment Analysis spreadsheet)
These are the amounts by which the value of fittings decreases over time. For tax purposes, this can be either a
constant amount (straight line or prime cost method) or a constant percentage of the written down value (diminishing
value method).
Depreciation on Building (Investment Analysis spreadsheet)
These are the amounts by which the value of the building decreases over time. See Capital allowance.
Eligible income (Debt Service Ratio dialog)
Eligible income is that part of gross income that a financial institution will accept as disposable. This could be 30% of
wages or salary (the remainder being required for paying tax and living expenses) and 80% of rental income.
Equity (Investment Analysis spreadsheet)
The equity in each year is the difference between the property value and the loan.
Ex-home loan payments (Linked Loan spreadsheet)
Under the linked loan model used in PIA, the regular home loan payments (ex-home payments) may be diverted to
repaying the investment loan once the home loan is repaid.
Exit stamp duty
Stamp duty payable on the sale of a property by the vendor.
Fittings
Items which are attached to a property but which can be removed without damaging the property. See also Depreciation
of fittings (chattels).
Fixtures
Items which are attached to a property and which cannot be removed without damaging the property.
Furniture package (Property Value dialog)
A furniture package is often purchased as an optional addition for a resort or holiday unit so that it can be rented fully
furnished. Furniture is a depreciable item and the cost is taken into account when calculating the total loan required.
However, for the purposes of the investment analysis, furniture is not considered as part of the property value subject to
capital growth.
General fittings (Depreciation of Fittings dialog)
When the property price is specified, PIA estimates the total value of fittings to a percentage of that property price.
Individual items are lumped 50:50 into the General fittings and Low value pool categories. If you choose to revise these
values, they will not change automatically when you change the property price.
Gross yield (Rental Income dialog)
The actual annual rent expressed as percentage of the property value (before deduction of rental expenses).
Home equity (Home loan analysis spreadsheet)
Home equity is the difference between the value of the home (principal residence) and the outstanding loan amount at
any point in time.
Home loan (Home loan analysis spreadsheet)
In PIA, home loan refers to the loan on the investor’s home or, more broadly, as a consolidation of all non-deductible
loans.
Indexed variables (Inflation Rate dialog)
By default, PIA uses the inflation rate to project rental income, rental expenses, taxable income and living expenses.
These are referred to as indexed variables, as their annual values are indexed to the rate of inflation.
Inflation rate (Investment Analysis spreadsheet)
The expected long-term average annual rate of inflation which determines the rate of increase in rent, rental expenses
(rates, insurance, etc) and any other taxable income.
PIA User Guide
Page 136
PIA Version 7.320
Appendix A
Glossary
Interest-only (I/O) loan
A loan type that allows the borrower to pay the interest only, without paying off the principal. Contrast Principal & interest
loan.
Interest rate
The percentage of the loan paid as a charge for the loan, normally expressed as annual interest.
Internal Rate of Return (IRR) (Investment Analysis spreadsheet)
The Internal Rate of Return (IRR) is a measure of the return on a series of cash flows where the time factor is taken into
account. Here it is calculated on the after-tax cash flow (your investment) and subsequent equity. (See 3.19.)
Investment capacity (Investment Capacity dialog)
This refers to the amount of investment property that an investor (or a couple) can afford after setting aside sufficient
monies to cover their normal living expenses.
Investment equity (Wealth Builder spreadsheet)
The investment equity is the difference between the total value of investment properties and the debt remaining on them.
If a property is sold, selling costs (sales commission and solicitor's fees) and capital gains tax must be deducted.
Investment surplus (Linked Loan spreadsheet)
Any positive cash flows from an investment property (usually because the net rent exceeds the loan repayments) can be
used to help repay non-deductible loans such as the home loan.
Licensee
The person (or company in the case of the Professional version) to whom a copy of the PIA software is licensed. The
nominated licensee is provided with a registration code. When these are entered into the Registration dialog, the software
switches from demonstration mode to full mode.
Linked loan (Linked Loan spreadsheet)
The term “linked loan” describes the situation whereby the repayment schedule for one loan (home loan) is dependent of
the repayment schedule of another loan (investment loan). A cash flow surplus from an investment property could be
used to make extra repayments on a home loan.
Living expenses (Living Expenses dialog)
Annual living expenses required by the investor (and partner) to cover cost of living (ranging from home loan repayments
to groceries, holidays, medical and school bills.
Loan amount (Loan Amount dialog)
The total of all costs (property price, purchase costs, loan costs etc.) less any initial outlays. If renovations were carried
out immediately, the loan would account for these costs as well. Additional loans may also be sought for items such as
furniture or to help supplement initial cash flows.
Loan cost write-off period (Loan Costs dialog)
The number of years over which loan cost can be written off for tax purposes. In Australia, the Loan cost write-off period
is 5 years or the term of the loan, whichever is lesser.
Loan costs (Loan Costs dialog)
Loan costs are made up of several components, some of which will be a function of the size of the loan while others may
be a flat fee. These will vary between institutions but as a general guide expect loan costs to be between 1 to 2% of the
total loan (including costs).
Loan eligibility
Criteria for establishing whether you would qualify for an investment loan under the criteria set down by any financial
institution.
Loan term
The number of years it will take to pay out the loan completely.
Loan type
The loan type can be interest only (I/O), principal and interest (P&I), interest capitalised (c) or a combination of the above
(split loan).
Loan value ratio (LVR)
The ratio of the total loan to the total value of the properties used as security for the loan.
Low-value pool (Depreciation of Fittings dialog)
These items are under $1000 and are to be written off using the diminishing value method at a rate of 37.5%, but at only
half this rate (18.75%) in the year of acquisition.
MAPI
Messaging Application Programming Interface – a Microsoft interface specification that enables various mail applications
and workgroup applications (including E-mail and fax) to run through a single client. MAPI is required for some of PIA’s
email-related functions.
Marginal rate, Marginal tax rate (Tax Credit dialog)
The rate of tax paid on the next dollar earned. Using this method for calculating tax credits makes the assumption that all
tax savings are made at this rate.
Market value (Property Value dialog)
The estimated current value of the property if sold.
PIA User Guide
Page 137
PIA Version 7.320
Appendix A
Glossary
Negative gearing
Purchasing an investment with borrowed funds where the interest on the loan exceeds the net rent from the investment.
Net cost per year (Home Loan and Linked Loan spreadsheets)
In relation to the Home Loan, the net cost is simply the cost of home loan payments less any redraws. In the case of the
Linked Loans, the total net cost is the combined cost of home loan payments (less any redraws) plus the net cost of the
investment property.
Net Present Value (NPV) (Internal Rate of return dialog)
This represents the increase in the investment (equity) in today's dollars. In other words, it represents the difference
between the market value of the investment (in today’s dollars) and it’s total cost (also in today’s dollars).
Net rent (Rental Income dialog)
Gross annual rent (that is, excluding vacancies) less rental (property) expenses.
Net worth (Wealth Builder spreadsheet)
The investor's net worth is the total value of all property assets, including the family home, less the total of all outstanding
loans.
Net yield (Rental Income dialog)
The annual rent less rental expenses expressed as a percentage of the property value. (See Rental expenses.)
Non-deductible expenses
Expenses that are not tax deductible. In PIA, these are household living expenses plus home loan repayments.
Investments (Investment Analysis spreadsheet)
Investments is a collective term covering the investor's entire contribution to the rental return and equity accumulation
process (i.e. it is needed to calculate the precise return on investment). Generally, Investments simply represents the
cash invested at the time of purchase. These cash investments can contribute to the purchase price (for example, a
property “deposit”), renovation costs, associated purchase costs and even loan costs (if there is a loan). Investments can
also represent cash invested after purchase (e.g. regular or lump sum loan repayments. Where you wish to simulate the
cash flows, starting now, on an existing investment property in which you already have substantial equity, Investments
would also include that existing equity.
PIA settings file
PIA has default settings for such things as stamp duty, sales commission and tax. These can be set via commands in the
Settings menu (see 7.6). The settings are stored a file in the program directory.
Portfolio analysis
During portfolio analysis, PIA collates of all the property and financial data on each of a selection of properties to
calculate the projected cash flows and rate of return for a specified time frame.
Pre-tax cash flow
These are all of the monies that flow in or out of your pocket before tax is taken into account. In most cases they
represent the gross rent less interest, expenses and any payments toward the principal. Interest and expenses are not
included in the cash flows if they are capitalised in the loan. Likewise, rents will not be included if they are used to directly
reduce the loan.
Pre-tax equivalent of IRR
The Pre-Tax Equivalent of the Internal Rate of Return provides a useful comparison with cash investments, as this is the
interest rate that you would need to achieve from a bank to get the same after-tax return. Any such interest would be
taxed at your top marginal rate. This is not to be confused with the Pre-Tax Internal Rate of Return, which is the return
calculated on your pre-tax cash flows.
Pre-tax Internal Rate of Return
The IRR calculated on your pre-tax cash flows.
Principal
The amount that you borrow from the lender. It is the 'capital' amount upon which interest is payable.
Principal & Interest (P&I) loan
A loan type where payments include both interest and principal components, reducing the balance of the loan to zero at
the end of the term. Contrast interest-only loan.
Principal residence
This is the investor’s own home, also known as Principal Place of Residence (PPOR).
Program directory
This is the directory (folder) containing the PIA program files. It is recommended that you create your own directory
(folder) for storage of your property files (see 7.1).
Property file
PIA saves all data relevant to the analysis of an individual property in a separate property file. This has the file extension
PIA. Note that settings – which are not property-specific, but cover items in the Settings menu such as Stamp Duty, Tax
Scales and Preferences – are stored in the Settings file, not the property file.
Property value (Investment Analysis spreadsheet)
The property (or market) value is the price you would reasonably expect to get for the property if it were to be sold.
Purchase costs (Investment Analysis spreadsheet)
Purchase costs are those extra costs associated with the acquisition of an investment property. These include solicitor's
fees (conveyancing costs) and stamp duty.
PIA User Guide
Page 138
PIA Version 7.320
Appendix A
Glossary
Real IRR, real return
The after-tax, after- inflation rate of return. See Internal Rate of Return.
Redraws (Home Loan Analysis spreadsheet)
When using a credit line, all income is paid into the loan account, reducing the principal. Redraws are funds withdrawn
from that account, for example to pay living expenses.
Refinance costs
Costs associated with refinancing or consolidating a loan, including establishment fees and registration of mortgage.
Registration code
When entered with the licensee’s name, the registration code “unlocks” the software, turning a demonstration version into
a full version.
Rental expenses
Expenses associated with a property that is rented (often referred to as rental expenses).
Rental income
Income derived from rent. Projected annual rents, which can be inflation-indexed or specified directly, represent the
potential rent less any vacancy factor.
Sales commission
Commission payable to the agent on sale of a property. Rates vary depending on location.
Settings file
Settings – which are not property-specific, but cover items in the Settings menu such as Stamp Duty, Tax Scales and
Preferences – are stored in the Settings file. The settings file has the filename extension SET and is stored in the default
folder.
Stamp duty
A tax payable on the transfer of title of a property. Stamp duty is an Australian State-based tax and is based on the sale
price of the property. See also Exit stamp duty.
Taxable income
Total income liable to income tax (such as salary, interest) less total allowable tax deductions.
Tax credits (Investment Analysis spreadsheet)
Tax credits can be thought of as tax refunds which, in PIA, are credited in the same year they occur. For a property in a
single name, tax credits can be calculated from the marginal tax rate or more precisely using taxable income. For a
property in joint names, the tax credits must be calculated using the taxable incomes of both partners ('joint'). For a
property purchased in joint names, you can specify the proportional ownership.
Tax liabilities
Tax payable to local, state or federal governments.
Tax savings (Tax Benefits dialog)
A reduction in tax liability resulting from a deductible expense (see Tax credits).
Tax scales
Scales used to calculate personal income tax liability. Scales vary from country to country (see 7.7).
Tax withholding variations
Adjustment of tax withheld. See Tax Variation Application in 7.2
Today’s dollars
Because of inflation, the purchasing power of a dollar in the future will be less than today. PIA uses discounted cash flow
methods (converting everything into today’s dollars) in order to make correct investment comparisons. See IRR and
NPV.
PIA User Guide
Page 139
PIA Version 7.320
Appendix B
Table of figures and dialogs
Appendix B – Table of figures and dialogs
About PIA
Accumulated Cash Savings
Advanced Options (Annual Rental Income)
After-tax Cash Flow
Agent’s Commission
Annual and Special Rental Expenses
Annual Interest Rates
Annual Investment
Annual Living Expense Budget
Annual Living Expenses
Annual Rental Income
Asset & Liabilities
Bank Comparison
Borrowing Capacity Calculator
Calculator
Capital Gains Tax Calculator
Capital Gains Tax Settings
Capital Growth Calculator
Color
Congratulations
Credit Line Payments
Current Investment Portfolio
Current Taxable Income
Customise Investment Portfolio
Customise Investment Report
Data Entry Check List
Debt Service Ratio
Delete Files
Depreciation of Building
Depreciation of Fittings
Disclaimer Text
Disclaimer
Elements of the PIA spreadsheets
Elements of the PIA window
Equity
Expense Item
Extra Payments
Feedback
Font
Help Topics
Home Equity
Home Loan Analysis spreadsheet
Home Loan Balance
Home Loan Details
Home Loan Refinance Costs
Home Loan Repayments
Home Loan Value Ratio
Home Value Growth
Income & Expenses
Income Tax Calculator
Inflation Calculator
Inflation Rate
Interest Rates
Internal Rate of Return
Investment Analysis spreadsheet
Investment Capacity
Investment Equity
Investment Loan Balance
Investment Property Growth
PIA User Guide
134
80
32
45
35
36
34
24
76
61, 77
32
113
104
118
114
116
124
114
131
6
24
108
107
108
102
9
81
97
38
39
127
127
12
10
28
76
59
133
128
132
54
50
63, 92
77
57
58
64
52, 68
113
115
115
30
62, 91
14, 46
17
110
84
26
20
Investment Surplus
90
Investments
23
Investor’s Notes
107
Investor’s Personal Details
106
Joint Net Income (Non-Property)
74
Land Tax Calculator
117
Land Tax Scale
117
Load Component
98
Loan Amount
4, 25
Loan Consolidation
55
Loan Costs
41
Loan Eligibility Calculator
119
Loan Interest & Type
27,33
Loan Payments Calculator
119
Loan Refinance Calculator
120
Loan Value Ratio (LVR)
72
Net Worth
83
Preferences
15, 18 129, 130
Pre-tax Cash Flow
37
Pre-tax Equivalent of IRR
47
Printer Page Setup
99
Property Details
97
Property Value
19
Purchase Costs
14, 21
Reapportion Variables confirmation
112
Registration Number
6, 132
Rental Expenses
35
Rental Income
31
Retirement Goals Calculator
120
Sales Commission Calculator
116
Save As
96, 97
Save Changes confirmation
99
Save Component
98
Select Current Portfolio Files
108
Select Directory
109
Selling Costs
125
Set Projections
18
Specify Consultant
126
Specify Country Defaults
8
Specify Sales Commission Scale
124
Specify Stamp Duty Scale
22
Specify Tax Scale
124
Stamp Duty Calculator
115
Structure of the Linked Loans spreadsheet
87
Superannuation Calculator
121
Tax Benefits
78
Tax Credits
43
Tax Variation Application
111
Total Annual Net Cost
94
Total Loan Value Ratio
93
Total Loans
70
Total Tax Deductions
42
Total Value
71
Wealth Builder spreadsheet
67
Wealth Maximizer
111
Welcome@
7
Whoops...
6
Window and sizing
16
Page 140
PIA Version 7.320
Appendix C
Abbreviations
Appendix C – Abbreviations
(c)
(d)
CL
CGT
CPI
DSR
FIN
INV
I/O
IRR
Indicates that amounts in this row have been “capitalised” (i.e. borrowed)
Indicates that the amounts in this row have been used to repay debt.
Credit Line
Capital Gains Tax
Consumer Price Index
Debt Service Ratio
Finance component file extension
Investor component file extension
Interest-only (loan)
Internal Rate of Return
LVR
(np)
NPV
(pf)
P&I
PIA
PIA
PIAFpu
PIAPro
PRP
SET
Loan Value Ratio
Indicates non-property.
Net Present Value
Existing portfolio included in Total inv. properties
Principal & interest (loan)
PIA property file extension
Property Investment Analysis
PIA Investor version (for personal use)
PIA Professional version (for professional use)
Property component file extension
Settings file extension
PIA User Guide
Page 141
PIA Version 7.320