Excel Lab - Creating Mortgage Calculate and Importing Data

Excel Lab - Creating Mortgage Calculate and Importing Data


Part A - Excel PMT Function

PMT function is very useful for calculating monthly payment required to payback a loan or mortgage at a fixed rate. This function require a minimum of three inputs, periodic rate, number of periods, present value or the loan amount. Here is a simple example.

Home Loan: 350,000.00
Interest rate: 4.5%
Number of years to repay the loan: 25

Note: To calculate monthly payment, we need to find the monthly rate and number of months as shown above. Then it is simply a matter of substituting the values into the payment function, as shown in the formula view below.

Part B - CUMIPMT and CUMPRINC function

CUMIPMT

Cumulative interest payment function allows you to calculate the interest paid for a loan or from an investment from period A to period B.

When getting a loan, CUMIPMT function can be used to calculate the total amount of interest paid in the first five months or from period 12 to period 20. A period can be a month, a week or two week.

Loan Amount : 350,000.00
APR: 4.5%
Down payment: 0.00
Years: 25

Payment per year: 12

From the above data, we can calculate the following:
No of Period: 25 × 12 = 300

Periodic Rate: 4.5/12 = 0.375%

Here is how you will substitute these values into the function.

= CUMIPMT (periodic rate, No of period, vehicle price, start period, end period,  )

= CUMIPMT (0.375, 300, 350000, 1, 5, 0)

In an excel worksheet, we use cell address instead of actual values as shown below:

Here is the formula view of the worksheet:

CUMPRINC

Another related function is CUMPRINC. CUMPRINC function is used to calculate cumulative principle paid for a given period. In the example given here, we are calculating cumulative principle paid in the first five month.

Part C - Importing External Data into Excel

Excel allows you to import data from various file formats. You can import data from websites, text files, XML files and many types of database files.

Here is a simple text file that contains the following data. In this dataset, each field is separated by a comma and each record starts in a new line.

To import above text file into an excel worksheet:

1. Create a new worksheet in the same workbook, select the cell where you want the data to be pasted.

2. Click the Data tab > click From Text >  Text Import Wizard dialog box opens

3. There are two options here, keep the default option selected. Check My data has headers.

4. Uncheck Tab and Select comma. Click Next.

5. In step 3 of the Text Import wizard dialog, select the first column and select text option, select the second column and check the text option and repeat for the third.

6. Click finish. A Import Data dialog appears, click OK to accept the default.

7. Now you should have successfully imported the comma separated text file into excel worksheet.