Ozgrid Excel Newsletter. Excel Newsletter Archives

Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee & Free Question Support 24/7

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.

Watch the latest videos on YouTube.com

Excel VBA Macro Codes

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

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