Note: This assignment requires screenshots of Excel work sheet. You may use the following instructions

to capture only what is pertinent and not the entire screen.

Windows:

$1.$ Press shift $+$ Windows key $+$ S

$2.$ Use Snipping Tool app

IOS:

$1.$ Press and hold these three keys together: Shift, Command, and $4.$

$2.$ Drag the crosshair to select the area of the screen to capture. To move the selection, press and hold

the Space bar while dragging. To cancel taking the screenshot, press the Esc $($Escape$)$ key.

$1.$ Alex, a first$-$year university student, dreams of taking a world tour after graduation in four years. To

fund this trip, Alex recently got a part$-$time job, so they decided they can save $$150$ at the end of each

month and expect to keep this payment for the next four years. The account will earn an annual

interest rate of $5\%$ compounded monthly. $(7$ marks$)$

$($a$)$ How much will Alex have saved by the time they graduate in four years if they continue with the

new savings plan and earn $5\%$ interest compounded monthly? $(3$ marks$)$

$($b$)$ If Alex decides instead that they want to have $$10,000$ saved for this world tour, assuming they

still save $$150$ a month and the account will earn $5\%$ compounded monthly, how long $($in years$)$

will they have to save for? $(4$ marks$)$

$2.$ Amir, a university student, has been saving for a study$-$abroad program for the last $4$ years. He has

been depositing $$150$ at the end of every month into a savings account. Given his current financial

situation, Amir has decided to increase his monthly savings to $$250.$ He plans to continue these

contributions for the next $2$ years. The savings account offers an interest rate of $4\%$ compounded

monthly throughout the entire $6-$year period. How much will Amir have saved altogether by the end

of the $6-$year period? $(7$ marks$)$

$3.$ Emiliana, a first$-$year business student, wants to save for a down payment on a home after graduation.

She plans to contribute $$3,000$ to her Tax$-$Free Savings Account $($TFSA$)$ at the end of each year,

hoping to accumulate $$150,000$ in $20$ years. What periodic annual rate of return should Emma look

for when shopping around for TFSAs achieve to reach her goal of $$150,000$ in $20$ years? Use Excel

Goal Seek to find the rate of return and round your answer to $2$ decimal places. Include two

screenshots or printouts in your submission: $(1)$ the Excel sheet displaying the formula view and $(2)$

the Excel sheet showing the calculated values. To switch to the formula view in Excel, press the

accent key $(`)$ and the Control key simultaneously. Press them again to return to the regular view. $(5$

marks$)$

$4.$ The Wilsons are establishing a Registered Educational Savings Plan $($RESP$)$ for their newborn

daughter. They would like to accumulate $$100,000$ in $18$ years by making bi$-$weekly $($every $2$ weeks$)$

MGSC $1205$ Introduction to Quantitative Methods $1$

Assignment #$2$

deductions from their bank account. The bank estimates that they should be able to earn $6\mathrm{.}5\%$ annual

interest on this plan. $(5$ marks$)$

$($a$)$ How much must they deduct every $2$ weeks to achieve this goal? $(3$ marks$)$

$($b$)$ In total, how much interest has been earned in the plan over the $18$ years? $(2$ marks$)$

$5.$ The Kim family buys a house for $$780,000.$ They pay $$156,000$ down and take out a $25-$year

mortgage on the balance. The interest rate is $6\mathrm{.}5\%,$ compounded monthly. $(17$ marks$)$

$($a$)$ What will the Kim$$s monthly payment be$?(3$ marks$)$

$($b$)$ What is the total interest the Kim family will pay over the $25-$year life of the loan? $(2$ marks$)$

$($c$)$ How much of their first payment, one month from now is for interest, and how much is

applied against the principal of the loan? $(3$ marks$)$

$($d$)$ Set up a mortgage amortization table in Excel for the first year. Include a screenshot$/$printout

in your answer. $(5$ marks$)$

$($e$)$ Use Excel to find out the balance owing on their loan after $10$ years. Include a screenshot or

printout highlighting this value. $(2$ marks$)$

$($f$)$ Use Excel to find out how much of their next monthly payment would be in interest after $10$

years and how much is applied against the principal. Include a screenshot or printout

highlighting both values. $(2$ marks$)$

$6.$ The Huskies Cafe is considering launching a new line of specialty beverages aimed at university

students. The project would require an initial investment of $$30,000$ at the end of Year $1,$ $$20,000$ at

the end of Year $2,$ and $$15,000$ at the end of Year $3.$ The expected annual profit from selling the new

beverages is $$12,000$ for Years $1$ to $3,$ and $$20,000$ for Years $4$ to $7.$ The startup$$s cost of capital is $5\%$

compounded annually. $(9$ marks$)$

$($a$)$ What is the Net Present Value $($NPV$)$ of the proposed beverage line? $($Negative values should

be indicated by a minus sign. Do not round your intermediate calculations. Round your final

answer to $2$ decimal places.$)(8$ marks$)$

$($b$)$ Should The Huskies Cafe introduce the new line of specialty beverages?