Using Google Spreadsheets to Calculate Your Mortgage
As interest rates head north many families will be sharpening their pencils bracing themselves for their increased mortgage rates. But what if you could do some calculations beforehand and even make some decisions to swap financial institutions or change the terms of your loans.
Thanks to Google Spreadsheets you can do this online if you don't already own a proprietary software copy. Googe's spreadsheets offer many of the financial functions that MS Excel does and the ones pertinent to our calculations are all there. So how do we go about it?
Firstly, login to the spreadsheets or register if this is your first Google account. When you first enter Google Labs for their spreadsheet software it will look like this;
Then, select a starting cell and begin adding some of the relevant information that you may want to play with. For instance, the Loan Amount (PV), the Loan Term in Years (NPER), the Interest Rate (RATE) and also the Residual Balance (FV) at the end of the Term. (This will most likely be zero).
The next part is to calculate the Payment (PMT) using the financial formulas. Click Formula (that's circled in red). A blue formula bar will appear with many of the common functions such as SUM, COUNT, AVERAGE etc but we're looking for the financial functions so click more>>
This will bring up the Insert A Function dialog box so while your destination cell (C8) is highlighted select the PMT function.
Now the complicated part begins - writing the formula. The PMT function has this syntax - =PMT(RATE,NPER,PV,FV,DUE). So, to use this with our variables that we've already added white the formula as such; =PMT(C5/12,C4*12,0-C3,C6,0).
The reason we use the 12 as a denominator for the NPER and multiplier of the RATE is because their are 12 months per year and mortgages compound monthly rather than on an annual basis. In fact, some accrue on a daily basis but we don't need to be that exact for this function.
The zero at the end of the formula is for when the first payment is due. If it's at the beginning of the month then use 0 else if it falls at the end of the month use 1.
You should end up with this result;
To format that number, while it's highlighted select Function and then click Choose Format and select the style you want to read it in (usually dollars and cents).
Now you can change any of those original variables to see what effect each has on the monthly payment.
Subscribe to this feed