FREE Excel STUFF
SearchSearch Excel Content
PRODUCTS
Development
Contact
 

Ozgrid Excel Newsletter. Excel Newsletter Archives  Excel Data Manipulation and Analysis

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

Excel Tips

Data Tables For Mortgages and Amortization

Download Demo

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

Excel VBA Macro Codes

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

Printing Pivot Tables By Page Field (Report Filter)

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

Print Each Page Field Of A PivotChart Code

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