Hi Dee
The three most common formulas I use are FV, PMT, and ISPMT.
FV = Returns the future value of an investment based on periodic, constant payments and a constant interest rate.
Syntax
FV(rate,nper,pmt,pv,type)
So in your eg the forumula would be:
=FV(12.46%,7,,-200000)
=$455,005.40
This means that your investment is growing at 12.46% per year on average for 7 years to reach a current value of $455,000 from $200,000.
If you want to assume a rate of growth of 6% and see what the investment would be worth in 10 years time the formula would be:
=FV(6%,10,,-455000)
= $814,835.70
The PMT function calculates P&I payments.
PMT = Calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax
PMT(rate,nper,pv,fv,type)
So in your eg assuming you have a $150,000 loan on this asset with an interest rate of 7%
=PMT(7%/12,30*12,-150000)
= $997.95 to equal your monthly loan repayment P&I.
If you just want interest only then use the ISPMT formula.
ISPMT = Calculates the interest paid during a specific period of an investment.
Syntax
ISPMT(rate,per,nper,pv)
=IPMT(7%/12,1,30*12,-150000)
= $875.00
This calculates your monthly interest payment.
The combined use of the function PMT and ISPMT allows you to calculate the difference in cashflow paying P&I loan and Interest only loan.
To factor in inflation, I calculate the FV of my costs and rents so that I can compare these with the value of the asset in the future. As an eg, if your current rents per year are $10,000 then what will they be in 10 years time assuming an inflation rate of 3%?
=FV(3%,10,,-10000)
=$13,439.16
One thing to keep in mind if what unit you are using whether it is month or year. If you are calculating monthly payments for both PMT and ISMPT then you need to divide your interest rate by 12 and multiply your terms by 12. If you are using fortnight then you divide interest rate by 26 and multiply your term by 26 etc.
Hope this helps, let me know if you have any questions in relation to the above.
Cheers
Corsa
