Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee & Free Question Support 24/7 FREE DOWNLOADS SmartVBA | SmartVB6 | CodeVBA | CodeVB6 | Code Generator Pro | TraderXL Pro Package | Catalog | MORE.. |
In these days of financial uncertainly, many people are concerned about their
mortgages and loans and the effects that various factors may have on them.
PMT
If you wanted to work out what your mortgage repayments would be if the interest
rate changed, you could use a very simple function called PMT.
Take this example:
We have a mortgage of $200,000
We have 12 mortgage payments a year over 30 years (360 payments in all)
The interest rate is 7.5%
· In A1 enter in $200,000
· In A2 enter in 360
· In A3 enter in 7.5%
· In A4 enter in =PMT(A3/12,A2,A1)
This should give you the result of -$1,398.43, which is the amount of money you
would need to pay monthly off your loan in order to pay it off within 360 months
at 7.5% interest rate.
DATA TABLES
This is all well and good if you just want the one scenario, but would be pretty
long winded if you wished to examine the effects of different interest rates on
your mortgage at the same time. We can use one of Excel’s built-in data
analysis tools to do this for us called Data Tables. With the use of Data
Tables we can substitute the values in the cells used in the PMT formula for
different values and thereby see the effects on our formula. There are two
types of Data Tables, One Variable Data Tables and Two Variable Data Tables.
ONE VARIABLE DATA TABLE
As the name suggests, we would use a One variable data table to gauge the
effects on our formula should we change just one of the variables that the
function relies on.
We will use the same worksheet.
Firstly, we need to set up our data so we can easily see what we are trying to
do.
· In cell D4 enter in the word Repayment
· In cells C6:C13 enter in the following interest rates: 6%, 6.5%, 7%,
7.5%, 8%, 8.5%, 9%, 9.5%
· In cell D5 enter in the formula =A4
Now to produce the table.
· Highlight the range C5:D13 and go to Data>Table to produce the Table
dialog box.
· Click in the Column input cell, then select cell A3 (note the cell
reference A3 has been inserted as an Absolute Reference-$A$3)
· Click OK.
You should see the results in cells D6:D13. These figures will be the mortgage
repayments if the corresponding interest rate in C6:C13 changes. Note that the
data table has created what are known as Array Formulas in D6:D13 to give us the
results we are looking for.
Array Formulas allow more complex calculations than
normal formulas, but be aware, too many of them WILL slow down recalculation
speed, saving, opening and closing.
TWO VARIABLE DATA TABLE
If you wanted to gauge the effects on your formula of changing two variables,
you would need to use a Two variable data table. We will again use the same
worksheet and scenario to see not only what effect different interest rates
would be on our loan, but also the effect of either reducing or increasing the
number of repayments would have.
Set up your data like this:
· In cells C19:C26 enter in the following interest rates: 6%, 6.5%, 7%,
7.5%, 8%, 8.5%, 9%, 9.5%
· In cell D18 enter 240
· In cell E18 enter 360
· In cell F18 enter 480
· In cell G18 enter 600
· In C18 enter in =A4
Now to produce the table
· Highlight the range C18:G26 and go to Data>Table to produce the Table
dialog box.
· Click in the Row input cell, then select cell A2 (note the cell
reference A2 has been inserted as an Absolute Reference)
· Click in the Column input cell, then select cell A3 (note the cell
reference A3 has been inserted as an Absolute Reference)
· Click OK.
You will now see the results of your loan repayment should you vary either the
number of repayments, or should the interest rate change.
More Excel Video Tutorials | Excel Help | Excel Formulas | Excel VBA | Excel Resources | Free Excel Training |
See Also: PivotTables | Refresh Pivot Table via Excel Macros || Hide/Show Pivot Table Field Items || Excel Subtotals || Making the SUBTOTAL Function Dynamic || Bold Excel Subtotals Automatically || Sum Every Nth Cell || Count of Each Item in a List || Grouping Pivot Tables Problems
Sub PrintAllPivotPageFields() Dim pt As PivotTable, pi As PivotItem, pf As PivotField Dim lLoop As Long ''Wrtten by www.ozgrid.com ''Prints out each PivotTable PageField Set pt = Sheet1.PivotTables(1) 'Change to suit ''http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm Set pf = pt.PageFields(1) 'Change to suit For Each pi In pf.PivotItems pi.Value = pi.Value If lLoop = 0 Then With Sheet1.PageSetup .CenterFooter = pi.Value .LeftHeader = pt.Name .LeftFooter = Now .PrintArea = pt.TableRange2.Address End With End If Sheet1.PrintOut lLoop =lLoop +1 Next pi End Sub
Sub PrintAllPivotChartPageFields() Dim pt As PivotTable, pi As PivotItem, pf As PivotField Dim lLoop As Long ''Wrtten by www.ozgrid.com ''Prints out each PivotChart PageField Set pt = Sheet1.PivotTables(1) 'Change to suit ''http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm Set pf = pt.PageFields(1) 'Change to suit For Each pi In pf.PivotItems pi.Value = pi.Value If lLoop = 0 Then With Chart4.PageSetup ''CodeName of PivotChart Chart Sheet ''http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm .CenterFooter = pi.Value .LeftHeader = pt.Name .LeftFooter = Now End With End If Chart4.PrintOut lLoop = lLoop + 1 Next pi End Sub
Common Searches Multi criteria: Sum, Count, Format | Loops, Toolbars | Charts: Dynamic, By Time | Delete: Blanks, By Condition | Report, Speed Up | Personal Budget | Mortgage | Schedule | Monte Carlo | Business Plan | Charting | Trading | Neural | Conversion | Estimating | Recover | Real Estate | Barcodes | Accounting | Forecast | Invoice & Inventory | Tax | Retirement | Investing | Database | Projects
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft