$bf{Spreadsheet;for;Compound;Interest}$

We shall use spreadsheets to obtain the balance for each compounding period. The formula for the spreadsheet cells is shown below.

If the annual interest rate $r$ is given, you must calculate the interest per compounding period as

$i=dfrac{r}{n}$

$$
n = left{ {begin{array}{c}
{1;;{text{for annually}}} \
{2{text{ for semi-annually}}} \
{4{text{ for quarterly}}} \
{12{text{ for monthly}}}
end{array}} right.
$$

The number of compounding period $m$ after $t$ years is

$$
m=ntimes t
$$

a.) In this case, $r=8.3%/aimplies i=0.083$ and the target FV=13000

The balance for each compounding period is shown.

b.) In this case, $r=6.8%implies i=0.068/2=0.034$ and target FV = $$4000$

c.) In this case, $r=12.4%implies i=0.124/4=0.031$ and target FV = $$4500$.

d.) In this case, $r=3.6%,;n=12$

$i = 0.036/12=0.003$

$$
m=12times 8=96
$$

$bf{Graphical;Solution;for;Loan/Amortization;with;Compound;Interest}$

We shall use graphical technique to obtain the interest rate. The intersection of the two graphs correspond to the solution.

If $r$ is the annual interest rate, then

$i=dfrac{r}{n}$

$$
n = left{ {begin{array}{c}
{1;;{text{for annually}}} \
{2{text{ for semi-annually}}} \
{4{text{ for quarterly}}} \
{12{text{ for monthly}}}
end{array}} right.
$$

The number of compounding period $m$ after $t$ years is

$m=ntimes t$

The regular payment can be calculated from the present value (initial balance) of the loan as

$R=dfrac{PVcdot i}{1-(1+i)^{-m}}$

a.) In this case, $PV=$2500$
$R=357.59$
$n=1$
$t=10$ years
$m = 1times10=10$

$R=dfrac{PVcdot i}{1-(1+i)^{-m}}$

$357.59=dfrac{2500cdot i}{1-(1+i)^{-10}}$

From the graph, $i=0.071$ or $r=7.1%/a$

b.) In this case
$PV=$15000$
$R=1497.95$
$n=2$ (semi-annually)
$t=6$ years
$m = 2times6=12$

$R=dfrac{PVcdot i}{1-(1+i)^{-m}}$

$1497.95=dfrac{15000cdot i}{1-(1+i)^{-12}}$

From the graph, $i=0.029$ or $r=0.029times 2=0.058=5.8%/a$

c.) In this case
$PV=$3500$
$R=374.56$
$n=4$ (quarterly)
$t=3$ years
$m = 4times 3=12$

$R=dfrac{PVcdot i}{1-(1+i)^{-m}}$

$374.56=dfrac{3500cdot i}{1-(1+i)^{-12}}$

From the graph, $i=0.0408$ or $r=0.0408times 4=0.1632=16.32%/a$

d.) In this case
$PV=$450$
$R=29.62$
$n=12$ (monthly)
$t=18/12$ years
$m = 12times (18/12)=18$

$R=dfrac{PVcdot i}{1-(1+i)^{-m}}$

$29.62=dfrac{450cdot i}{1-(1+i)^{-18}}$

From the graph, $i=0.0185$ or $r=0.0185times 12=22.2%$

a.) $7.1%/a$

b.) $5.8%/a$

c.) $16.32%/a$

d.) $22.2%$

$bf{Spreadsheet;for;Future;Value;of;Annuities}$

The future value of an regular $R$ investments after $m$ compounding periods

$FV=Rcdot dfrac{(1+i)^m-1}{i}$

$i=dfrac{r}{n}$

$$
n = left{ {begin{array}{c}
{1;;{text{for annually}}} \
{2{text{ for semi-annually}}} \
{4{text{ for quarterly}}} \
{12{text{ for monthly}}}
end{array}} right.
$$

The number of compounding period $m$ after $t$ years is

$$
m=ntimes t
$$

We need to find $R$ such that $FV=3500$

$r=12.6%implies i=dfrac{0.126}{12}=0.0105$

$t=2.5$ years

$m=2.5times 12=30$

$FV=Rcdot dfrac{(1+i)^m-1}{i}$

$3500=Rcdot dfrac{(1+0.0105)^{30}-1}{0.0105}$

$R=3500cdot dfrac{0.0105}{1.0105^{30}-1}=$99.86$

$$
$99.86
$$

$bf{Spreadsheet;for;Loan/Amortization;with;Compound;Interest}$

We shall use spreadsheets to obtain the balance for each compounding period. The formula for the spreadsheet cells is shown below.

If the annual interest rate $r$ is given, you must calculate the interest per compounding period as

$i=dfrac{r}{n}$

$$
n = left{ {begin{array}{c}
{1;;{text{for annually}}} \
{2{text{ for semi-annually}}} \
{4{text{ for quarterly}}} \
{12{text{ for monthly}}}
end{array}} right.
$$

The number of compounding period $m$ after $t$ years is

$m=ntimes t$

The payment stops when the balance becomes less than or close to zero, and the last payment is the sum of the regular payment and the last balance.

The regular payment can be calculated from the present value (initial balance) of the loan as

$R=dfrac{PVcdot i}{1-(1+i)^{-m}}$

a.) In this case, $r=0.0660$, $n=12$, $t=7$ years

$i=0.0660/12=0.0055$

$m=12times 25=300$

$R=dfrac{PVcdot i}{1-(1+i)^{-m}}=dfrac{120000(0.0055)}{1-(1+0.0055)^{-300}}=$817.76$

The spreadsheet is as follows.

b.) If he decides to pay $15,000$ on the first month after 3 years (37th month), we can obtain the following spreadsheet.

b.) The loan will be completely paid on the 238$^{th}$ month which is $boxed{bf{62; months; earlier}}$.

c.) Thus, the interest saved is $125330.22-88962.31=boxed{bold{$36,367.91}}$

a.) $$817.76$

b.) 62 months earlier

c.) $$36,367.91$

The future value of an regular $R$ investments after $m$ compounding periods

$FV=Rcdot dfrac{(1+i)^m-1}{i}$

If the annual interest rate $r$ is given, you must calculate the interest per compounding period $i$ as

$i=dfrac{r}{n}$

$$
n = left{ {begin{array}{c}
{1;;{text{for annually}}} \
{2{text{ for semi-annually}}} \
{4{text{ for quarterly}}} \
{12{text{ for monthly}}}
end{array}} right.
$$

The number of compounding period $m$ after $t$ years is

$$
m=ntimes t
$$

We shall calculate the future worth of both investment after 7 years:

Bank A: $r=6.6%$ monthly compounding

$i=0.066/12=5.5times 10^{-3}$

$FV=Rcdot dfrac{(1+i)^m-1}{i}=750cdot dfrac{(1+0.0055)^{7(12)}}{0.0055}=$79,805.09$

Bank B: $r=7.8%$ monthly compounding

$i=0.078/12=6.5times10^{-3}$

$FV=Rcdot dfrac{(1+i)^m-1}{i}=750cdot dfrac{(1+0.0065)^{7(12)}}{0.0065}=$83,456.12$

$$83,456.12-$79,805.09=boxed{bf{$36,651}}$

Therefore, they will earn $$36,651$ if they choose bank B.

$$
$36,651
$$

$bf{Spreadsheet;for;Loan/Amortization;with;Compound;Interest}$

We shall use spreadsheets to obtain the balance for each compounding period. The formula for the spreadsheet cells is shown below.

If the annual interest rate $r$ is given, you must calculate the interest per compounding period as

$i=dfrac{r}{n}$

$$
n = left{ {begin{array}{c}
{1;;{text{for annually}}} \
{2{text{ for semi-annually}}} \
{4{text{ for quarterly}}} \
{12{text{ for monthly}}}
end{array}} right.
$$

The number of compounding period $m$ after $t$ years is

$m=ntimes t$

Since he adds $$50$ every after 2 years (24 months), the new payment is changed on the 25th, 49th, and 73rd month.

Therefore, the loan is completely paid on the 96th month or $boxed{bf{after; 8; years}}$.

[begin{gathered}
{mathbf{Given:}} hfill \
PV = 150000 hfill \
{text{compounded monthly}} hfill \
r{text{ for first 5 years}} = 4.2% ,, Rightarrow i = 0.042/12 = 0.0035 hfill \
r{text{ after 5 years = 7}}{text{.5% }} Rightarrow {text{ }}i = 0.075/12 = 0.00625 hfill \
t = 20{text{ years}} hfill \
{text{R for first 5 years = $ 924}}{text{.86}} hfill \
hfill \
{mathbf{Solution:}} hfill \
{text{a}}{text{.) We shall calculate the }}FV{text{ of the regular payments she made }} hfill \
{text{for the first 5 years}} hfill \
FV = R cdot frac{{1 – {{left( {1 + i} right)}^{ – m}}}}{i} = 924.86 cdot frac{{{{1.0035}^{12left( 5 right)}} – 1}}{{0.0035}} = 61628.923 hfill \
{text{Thus, her remaining balance is the difference future value of the loan}} hfill \
{text{and the future value of her regular payments}} hfill \
{text{Balance = }}150000{left( {1.0035} right)^{12left( 5 right)}} – 61628.923 = 123,354.95 hfill \
hfill \
{text{She wants to completely pay the loan in the next 15 years}} hfill \
{text{with a higher interest rate of }}i = 0.00625 hfill \
{text{thus the present value of her payments for the next 15 years}} hfill \
{text{should be equal to her balance after 5 years}}{text{.}} hfill \
PV = R cdot frac{{1 – {{left( {1 + i} right)}^{ – m}}}}{i} Rightarrow R = frac{{PV cdot i}}{{1 – {{left( {1 + i} right)}^{ – m}}}} hfill \
R = frac{{123,354.95left( {0.00625} right)}}{{1 – {{left( {1.00625} right)}^{ – 12left( {15} right)}}}} approx boxed{$ ,{mathbf{1143}}{mathbf{.52}}} hfill \
{text{Therefore, her new monthly payment after 5 years is $ 1143}}{text{.52}} hfill \
hfill \
end{gathered} ]

[begin{gathered}
{text{b}}{text{.) If she keeps her payments the same,}},R = 924.86;{text{for the}} hfill \
{text{next 15 years}} hfill \
PV = R cdot frac{{1 – {{left( {1 + i} right)}^{ – m}}}}{i} hfill \
123,354.95 = 924.85 cdot frac{{1 – {{left( {1 + 0.00625} right)}^{ – m}}}}{{0.00625}} hfill \
{1.00625^{ – m}} = 1 – 123,354.95 cdot frac{{0.00625}}{{924.85}} hfill \
{1.00625^{ – m}} = 0.166385 hfill \
end{gathered} ]
\
As shown from the graph, $mapprox 288$ years\\
$288-15(12)=108 text{months} = 9$ years\\
Therefore, it take her $boxed{bf{9;years;more}}$ if she pays the same amount for the next 15 years.

a.) $$1143.52$

b.) 9 years more

[begin{gathered}
{mathbf{Given:}} hfill \
PV = 2754 hfill \
{text{downpayment = $ 350}} hfill \
R = $ 147{text{ monthly}} hfill \
{text{compounded monthly}} hfill \
t = 1.5{text{ years}} hfill \
{text{R for first 5 years = $ 924}}{text{.86}} hfill \
hfill \
{mathbf{Solution:}} hfill \
{text{a}}{text{.) }} hfill \
m = 1.5 times 12 = 18 hfill \
{text{We shall calculate the }}PV{text{ of his monthly payments}} hfill \
PV = R cdot frac{{1 – {{left( {1 + i} right)}^{ – m}}}}{i} = 147 cdot frac{{1 – {{left( {1 + i} right)}^{ – 18}}}}{i} = 2754 – 350 hfill \
frac{{1 – {{left( {1 + i} right)}^{ – 18}}}}{i} = frac{{2404}}{{147}} hfill \
end{gathered} ]

Therefore, $i=0.0103$ and $r=12times 0.0103=boxed{bf{12.36%/a}}$

$$
12.36%/a
$$

a.) If the interest rate doubles, you DON’T NECESSARILY double the payment to keep the same amortization period.
For example, if you borrowed $$1000.00$ at $4%$ compounded annually, you can complete the loan in 10 years if you pay $$123.29$ per year. However, if the rate is doubled to $8%$ and you pay double $2times123.2=246.58$, the loan will be completed in 5 years, not 10 years.

at $4%$ and $R=123.29$

b.) If you double the amount you borrowed, you NEED to double the amount of regular payment to keep the same amortization period.

For example, at $P=1000$ at $r=4%$, if you want to borrow $$2000$ instead, you need to pay $123.29times2=246.58$. Otherwise, the amortization period would be different.

To further prove this, remember the annuity formula

If you borrow PV, you need to make regular $R_1$ payments,

$PV=R_1cdot dfrac{1-(1+i)^{-m}}{i}$

If you borrow a double amount, you must pay $R_2$

$2PV=R_2cdot dfrac{1_(1+i)^{-m}}{i}$

If we equate $PV$ from the two equations

$R_1cdot dfrac{1-(1+i)^{-m}}{i}=dfrac{1}{2}cdot R_2cdot dfrac{1_(1+i)^{-m}}{i}$

$R_2=2cdot R_1$

Thus, you need pay double if you want to borrow double.

[begin{gathered}
{mathbf{Given:}} hfill \
PV = 50000 hfill \
t = 10{text{ years}} hfill \
r = 6.6% /a Rightarrow i = 0.066/12 = 0.0055 hfill \
{text{compounded monthly}} hfill \
hfill \
{mathbf{Solution}}: hfill \
{text{The amount that she has to pay in the first 4 years is}} hfill \
R = frac{{PV cdot i}}{{1 – {{left( {1 + i} right)}^{ – m}}}} = frac{{50,000 cdot left( {0.0055} right)}}{{1 – {{left( {1 + 0.0055} right)}^{ – 10left( {12} right)}}}} = $ 570.29 hfill \
{text{Her balance after 4 years is the difference between the future value }} hfill \
{text{of these payments and the future value of the loan after 4 years}}{text{.}} hfill \
{text{Balance after 4 years }} = 50000{left( {1 + 0.0055} right)^{4left( {12} right)}} – 570.29 cdot frac{{{{left( {1 + 0.0055} right)}^{4left( {12} right)}} – 1}}{{left( {0.0055} right)}} hfill \
{text{Balance after 4 years}} = ,33829.56 hfill \
{text{If she doubles her payment after 4 years,}},, hfill \
R = 2 times $ 570.29 = 1140.58 hfill \
PV = R cdot frac{{1 – {{left( {1 + i} right)}^{ – m}}}}{i} hfill \
33829.56 = 1140.58 cdot frac{{1 – {{left( {1 + 0.0055} right)}^{ – m}}}}{{0.0055}} hfill \
{1.0055^{ – m}} = 1 – frac{{33829.56left( {0.0055} right)}}{{1140.58}} hfill \
{1.0055^{ – m}} = 0.83687 hfill \
end{gathered} ]

She would need to pay 33 months more after four years, that is a total of

$4times12+323=81$ months instead of 10 years (120 months).

Therefore, she would complete the loan $120-81$ = $boxed{bf{39;months; earlier}}$.

$bf{Spreadsheet}$

$$
bf{Graphing;Calculator}
$$

[begin{gathered}
{mathbf{Given:}} hfill \
hfill \
r = 3.6% /a,,,{text{compounded monthly}} hfill \
t = 5{text{years}} hfill \
$ {text{ 250 discount if paid in cash}} hfill \
{text{bank loan }}r = 4.8% /a,,{text{compounded annually}} hfill \
hfill \
{mathbf{Find}}:{text{ Guitar price to make bank loan worthwhile}} hfill \
hfill \
{mathbf{Solution:}} hfill \
{text{Let }}x{text{ be the price of the guitar}} hfill \
{text{For store – financed purchase, the amount to be paid is}} hfill \
{A_S} = ;x{left( {1 + frac{{0.0036}}{{12}}} right)^{5left( {12} right)}} hfill \
{text{If you borrow from a bank and pay the guitar in cash}} hfill \
{A_B} = left( {x – 250} right){left( {1 + 0.048} right)^5} hfill \
{text{We need to find the value of }}x{text{ such that }}{A_B} leq{A_S} hfill \
left( {x – 250} right){left( {1 + 0.048} right)^5} leq ;x{left( {1 + frac{{0.0036}}{{12}}} right)^{5left( {12} right)}} hfill \
end{gathered} ]

Therefore, if the price is less than or equal to $$4967.58$, it’s better to pay in cash and loan from the bank, otherwise, the guitar store financing is better.

The guitar price should not exceed $$4967.58$.

[begin{gathered}
{mathbf{Given:}} hfill \
hfill \
r = 8% /a,;{text{charged semi – annually}} hfill \
{text{R = $ 1000}} hfill \
t = 20{text{ years}} hfill \
{mathbf{Find}}:{text{ Time to pay for each cases}} hfill \
hfill \
{mathbf{Solution:}} hfill \
{text{We shall find the present value of the mortgage}} hfill \
{text{Since the interest is charged semi – annually, we shall}} hfill \
{text{find its equivalent interest rate for monthly payments}} hfill \
{left( {1 + frac{{0.08}}{2}} right)^2} = {left( {1 + i} right)^{12}} Rightarrow 1 + i = {1.04^{2/12}} Rightarrow i = {1.04^{1/6}} – 1 hfill \
PV = R cdot frac{{1 – {{left( {1 + i} right)}^{ – m}}}}{i} = 1000 cdot frac{{1 – {{left( {{{1.04}^{2/12}}} right)}^{ – 20left( {12} right)}}}}{{{{1.04}^{1/6}} – 1}} = 120720.83 hfill \
hfill \
{text{a}}{text{.) $ 500 semi – monthly}};left( {12 times 2 = 24{text{ payments a year}}} right) hfill \
{text{Since the payment period is different from the period at which the interest}} hfill \
{text{is charged, we shall find equivalent }}i{text{ per payment period}} hfill \
{left( {1 + frac{{0.08}}{2}} right)^2} = {left( {1 + i} right)^{24}} Rightarrow left( {1 + i} right) = {1.04^{2/24}} Rightarrow i = {1.04^{1/12}} – 1 hfill \
120720.83 = 500 cdot frac{{1 – {{left( {{{1.04}^{1/12}}} right)}^{ – 24t}}}}{{{{1.04}^{1/12}} – 1}} hfill \
241.44166 = frac{{1 – {{left( {{{1.04}^{1/12}}} right)}^{ – 24t}}}}{{{{1.04}^{1/12}} – 1}} hfill \
boxed{{mathbf{t = 19}}{mathbf{.92 years}}} hfill \
end{gathered} ]

[begin{gathered}
{text{b}}{text{.) $ 500 biweekly}};left( {52/2 = 26{text{ payments a year}}} right) hfill \
{text{Since the payment period is different from the period at which the interest}} hfill \
{text{is charged, we shall find equivalent }}i{text{ per payment period}} hfill \
{left( {1 + 0.04} right)^2} = {left( {1 + i} right)^{26}}, Rightarrow left( {1 + i} right) = {1.04^{2/26}} Rightarrow i = {1.04^{1/13}} – 1 hfill \
120720.83 = 500 cdot frac{{1 – {{left( {{{1.04}^{1/13}}} right)}^{ – 26t}}}}{{{{1.04}^{1/13}} – 1}} hfill \
241.44166 = frac{{1 – {{left( {{{1.04}^{1/13}}} right)}^{ – 26t}}}}{{{{1.04}^{1/13}} – 1}} hfill \
boxed{{mathbf{t = 16}}{mathbf{.67}};{mathbf{years}}} hfill \
end{gathered} ]

[begin{gathered}
{text{c}}{text{.) $ 250 weekly}};left( {52{text{ payments a year}}} right) hfill \
{text{Since the payment period is different from the period at which the interest}} hfill \
{text{is charged, we shall find equivalent }}i{text{ per payment period}} hfill \
{left( {1 + 0.04} right)^2} = {left( {1 + i} right)^{52}}, Rightarrow left( {1 + i} right) = {1.04^{2/52}} Rightarrow i = {1.04^{1/26}} – 1 hfill \
120720.83 = 250 cdot frac{{1 – {{left( {{{1.04}^{1/26}}} right)}^{ – 52t}}}}{{{{1.04}^{1/26}} – 1}} hfill \
482.88332 = frac{{1 – {{left( {{{1.04}^{1/26}}} right)}^{ – 52t}}}}{{{{1.04}^{1/26}} – 1}} hfill \
boxed{{mathbf{t = 16}}{mathbf{.64 years}}} hfill \
end{gathered} ]

This time, we need to add additional column called “Annual Interest Rate” since the interest rate changes every year. Starting from $13%$, it goes down by $0.5%$ every after 12 months. We can automate this using this formula

$=0.13-0.005timesleft[text{FLOOR.MATH}left(dfrac{text{Payment Number}}{12}-0.00001right)right]$

The FLOOR.MATH function rounds down any value to integer value. Since the new rate takes effect every after 12 months (such as 13th, 25th, 37th and so on), we need to subtract an arbitrary tiny value 0.00001, otherwise, the new interest rate will start on the 12th, 24th, 36th, and so on.

Alternatively, you can manually change the annual interest range every after 12 months.

Therefore, the loan will be paid completely after 62 months or $boxed{bf{5 ;years ;and; 2; months;}}$.