III. Mortgages in Excel

Extraordinary Families, a foster family agency, needs to borrow $250,000 in the form of a

mortgage to update family meeting and overnight space to comply with state law. It agrees to

repay this mortgage in monthly payments over 10 years, making a single payment of principal

and interest at the end of each month. It agrees to a 4 percent annual interest rate on the loan.

(A) Use the PMT function in Excel to determine what the monthly payment will be. An

explanation is found on pp. 218-219 of Finkler. Please remember that you need to

divide the 4 percent interest rate by 12 to reflect monthly rather than annual payments,

and multiply the period (10) by 12 as well.

(B) One of the board members thinks that the organization should limit its mortgage

payment to $1500 per month, and fund-raise to pay whatever "balloon" payment would

be required at the end of the payment period. They want to know what the amount of

the remaining liability would be.

Use the FV function in excel to determine how much liability would be left at the end

with the same terms listed above, and a $1,500 monthly payment. Hint: be sure to

enter $1,500 as a negative in the formula. If you need help with the formula, refer to

this resource.