Excel Function: PMT

Just thought I’d share a useful Excel command you can use to calculate your monthly mortgage payments.

PMT function
This is used to determine the mortgage payment you have to make on your loan. The formula to key in is “=PMT(interest rate, number of periods, initial amount)”.

Example:

Initial Loan Amount A 500,000
Interest Rate B 1.50%
Number of periods C 360
Monthly Payment =PMT(B/12, C, -A) $1,725.60

 Key points to note when using this function:
1. the interest rate has to be divided by 12 since it is quoted on a annual basis while our payments are made monthly; and
2. the initial amount is negative since it is an inflow vs payments which are outflows.

Other than just calculating the monthly payment, I typically also use it to calculate the split between interest and principal payments of my monthly payments. To illustrate, please see the table below continuing the previous example:

Month Principal Balance Interest Principal Payment
0 $500,000.00
1 $498,899.40 $625.00 $1,100.60 $1,725.60
2 $497,797.42 $623.62 $1,101.98 $1,725.60
3 $496,694.07 $622.25 $1,103.35 $1,725.60
4 $495,589.33 $620.87 $1,104.73 $1,725.60
5 $494,483.22 $619.49 $1,106.11 $1,725.60
. . . . .
. . . . .
. . . . .
355 $8,595.74 $12.89 $1,712.72 $1,725.60
356 $6,880.89 $10.74 $1,714.86 $1,725.60
357 $5,163.89 $8.60 $1,717.00 $1,725.60
358 $3,444.74 $6.45 $1,719.15 $1,725.60
359 $1,723.45 $4.31 $1,721.30 $1,725.60
360 ($0.00) $2.15 $1,723.45 $1,725.60

 Interest is calculated by multiplying the previous balance by the monthly interest rate (in this example, it would be 1.50% divided by 12). Principal component is simply the monthly payment minus the interest component. Principal balance is then the previous principal minus the principal repayment.

From here, you may want to expand the table even further by adding in the rent you are getting and calculate the free cash flow you are generating from renting out your property. As you can probably see by now, the table can be as extensive as you want it to be, depending on how accurately you want to model your property cash flow.

Hopefully this is as useful to you as it is for me. Happy Excel-ing!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s