Pre-Calc Assignment: Goal: To use recursive sequence to determine the amount money owed on a loan after months Role You are a loan officer at a mortgage company.

Question

1. Audience: Jim and Joan Miller are customers applying for a mortgage. Situation: Jim and Joan Miller are borrowing $120,000 at 6.5% per annum compounded monthly for 30 years to purchase a home. Their monthly payment is determined to be $758.48.
2. Performance Task: You need to present Jim and Joan with a report detailing the following: A recursive formula for their balance after each monthly payment has been made. A determination of Jim and Joan's balance after the first payment. Use a spreadsheet or graphing utility to create a table showing their balance after each monthly payment.
3. Determine when the balance will be below $75,000. Determine when the balance will be paid off.
4. Determine the interest expense when the loan is paid.
5. Standards for Success: You will form an accurate recursive formula. You will accurately determine the balance after the first payment. You will accurately and completely construct a chart showing balances after each monthly payment. You will accurately determine when the balance will be below $75,000 and when it will be paid off. You will accurately determine the total interest expense incurred.

Please use recursive formula and/or compound interest.

Answer 1

Look Zzz Z.,

Recursive is not exactly the right term to use when you're calculating through a spreadsheet, but the idea is the same.

For each month (make column A = month number, you can make this fancier as two columns with one displaying "years" and the other "and months"), column B = incoming balance at start of month (initial loan amount for first row, otherwise the previous month's (balance + interest - payment) ; column C = interest charged on current month = monthly interest rate * incoming balance ; column D = cumulative interest paid = previous month's cumulative interest + current month's interest

All of this goes smoothly as pie on Excel, or any other spreadsheet program. You will need to establish a formula for each calculated column (with the exception of some first row values), then copy this formula block downwards. Fill the month range as you need, and extend the formulas downwards, until you have your values.

You will need to use spreadsheet (inbuilt) rounding functions, also, since you must calculate all interests, payments, and balances as dollars and cents.

Guess you might say, a problem like this might hold your interest for a while?

-- Cheers, --Mr. d.