Excel Financtial Functions

Question

Calculate the total monthly payment: You want to buy a house that costs $250,000, you have $25,000 for a

down payment, loan rate interest is 3.5% a year compounded monthly and you plan to get a 25-year loan

with a balloon payment of $30,000. Insurance costs $450/year and property taxes are 8% a year of the

assessed value of the house. The assessed value is $120,000.

You will need to use the PMT function to calculate the terms of the mortgage, and then you will need to

manually add the monthly payments required to service both the insurance costs and property taxes.

Remember to keep in mind whether you are paying the money or gaining the money.

Note: a balloon payment is where you take out a mortgage and after you have completed all the payments,

you still owe this extra amount when the duration of the loan is completed. It can be entered in your

equation is a future value of the loan.

Pro tip: You probably don’t want to get a mortgage on a house with a balloon payment.

Answer 1

Part 1: Calculating the monthly Mortgage Payment:

Given Information:

Monthly Interest: 3.5% / 12 (calculated from 3.5% annual interest compounded monthly)

Number of months: 25 * 12 months = 300 months

Total loan amount: $ 250,000 - $ 25,000 (down payment) = $ 225,000

Balloon Payment: $ 30,000

Either of the following Excel formulas will give you the correct amount for the mortgage payment alone:

=PMT((3.5% / 12), (25 * 12), (-250000 + 25000), 30000)

=-PMT((3.5% / 12), (25*12), (250000 - 25000), -30000)

Note: In both cases, you will see that the down payment, monthly payment, and balloon payment all have the same sign; and the home value has the opposite sign.

Part 2: Calculating the monthly insurance and taxes

Given Information:

Insurance: $ 450.00 / 12

Taxes: $ 120,000 * 8% / 12

Part 3: Combining the monthly Mortgage Payment, Insurance and Taxes:

Formula for Monthly Payment:

=PMT((3.5%/12), (25*12), (-250000+25000), 30000) + (450 / 12) + (120000 * 8% / 12)

You may want to simplify the formula (e.g. changing (-250000 + 25000) to -225000) to make it shorter. I decided not to do that--and added unneeded parentheses--because I wanted the way I reached each value to be shown.