Question

Name a worksheet as 'Goal Seek' and label cells as shown below

A	B
Loan Repayment	Base Scenario
Interest Rate
# of Payments
Principal
Payment
Total Payment
Total Interest

Using the following values, calculate Payment, Total Payments, and Total Interest for your Base Scenario. Format all payment fields as currency ($) with appropriate decimals and format all other fields correctly.   Payment is assumed at the beginning of the period.

Interest Rate – 4.34% (annual interest rate)| # of Payments - 360 (30-year mortgage) | Principal - $875,950 (1 pt)

When you present these figures to your clients, they say the maximum monthly payment they can - afford is $3750.50. Using the same initial values for Interest Rate, # of Payments, and Principal, use Goal Seek to adjust the following variables for the maximum payment the client can afford (i.e., you need to create three scenarios, label them 'Revised 1', 'Revised 2', 'Revised 3'):

Revised 1 - Principal (1 pt)

Revised 2 - # of Payments (1 pt) – Make it a whole number, ROUNDUP

Revised 3 - Interest Rate (1 pt) – 2 decimal points

Answer 1

1	B	C	D	E	F
2	A	B
3	Loan Repayment	Base Scenario
4	Interest rate	4.34%
5	# of payments	360
6	Principal	$    8,75,950.00
7	Payment	$          4,355.43	-PMT(C4/12,C5,C6)
8	Total payment	$ 15,67,953.02	=C7*C5
9	Total Interest	$    6,92,003.02	=C8-C6
10	(ii)
11	Payment =	$          3,750.00
12	Revised 1 =
13	Principle	$    7,54,188.73	-PV(C4/12,C5,C11)
14	Revised 2 =
15	# of payments	169.6259034	-NPER(C4/12,C11,C6)
16	Revised 3 =
17	Interest Rate	3.120%	=RATE(C5,C11,-C6)*12