Question

AutoSave Off H File Home e r - buad5707-m 1-novice-workbook (2) - Ex... Page Layout Formulas Data Review View Searc Help Insert J29 - X ✓ fx A B D E F G H I J K L M 2 3 4 Practice Prob. 6) The stock and bond markets have become a difficult for you to stomach without a strong antacid. Your billionaire son-in-law, who is a titan in the world of real estate finance, suggests that you might want to invest in real estate, particularly single family and multi-family dwellings. This makes sense to you and you decide to build a flexible model to answer financing questions. The PMT(rate,nper,pv) function in Excel is your focus since you are keenly aware of cash flow issues resulting from bank loans. It contains 3 arguments: Rate, Number of Periods (Term), and Present Value (Principal). The model is shown below with particular values. Create a Scroll Bar that controls the value of nper and locate it next to the nper value in the model. Make the lower limit 120 and the upper limit 480. Also, make the increment change 12 and the page change 60. Use 3% annual rate and 150,000 as pv. 13 15 16 By the way, what happens to the $ reduction in payments as the term of the loan becomes longer? Is the change what you expected? rate (annual)=1 nper (mns)= pv ($)= 3.00% 360 150000 27 28 Monthly $ outflow= 29 30

Answer 1

ДА B rate (annual) = nper (mns) pv ($) = 3.00% 120 1,50,000 $ 3.00% 180 1,50,000 3.00% 240 1,50,000 $ 3.00% 300 1,50,000 3.00% 360 1,50,000 $ 3.00% 420 1,50,000 3.00% 480 1,50,000 $ $ $ $ asoo's 2,501 $ 4,500 $ Monthly $ outflow = $ 4,633 $ 4,522 $ 4,504 $ 4,501 $ 4,500 $ 4,500 $ 4,500 When the term of loan gets longer, the monthly outflow gets reduceed.

Formulas used

2 A B 0.03 rate (annual) = nper (mns) pv ($) = 120 0.03 =+C3+60 150000 0.03 =+D3+60 150000 0.03 =+E3+60 150000 0.03 =+F3+60 150000 0.03 =+G3+60 150000 0.03 =+H3+60 150000 150000 Monthly $ outflow = =PMT(C2,C3,-C4,0,0) =PMT(D2,D3,-D4,0,0) =PMT(E2,E3,-E4,0,0) =PMT(F2,F3,-F4,0,0) =PMT(G2, G3,-G4,0,0) =PMT(H2,H3,-H4,0,0) =PMT(12,13,-14,0,0)