FREE DOWNLOADSSmartVBA | 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.
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.
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