11. Two years ago, Molloy Business Division constructed a state of the art high
ID: 2755256 • Letter: 1
Question
11. Two years ago, Molloy Business Division constructed a state of the art high technology classroom which will stand as a testimony to our commitment to business and education. They anticipate that the room will require an upgrade in three years to maintain this commitment. They have decided to set up a sinking fund with quarterly payments to fund the upgraded classroom. They anticipate the cost in three years will be $120,000. How much must they deposit each period if the account promises a return of 6.2% compounded quarterly? For tax purposes the accountant needs to calculate the interest each year. Create a table in Excel which will calculate each payment and the amount of interest accrued in the fund each year. The fund will earn 6.2% interest compounded quarterly. with fiancee calculator or excel
Explanation / Answer
1.
Amount required after 3 years = $120,000
Interest compounded quarterly.
Hence, No. of periods = n = 3 * 4 = 12
Effective rate interest = r = 0.062/4 = 0.0155
Future value of annuity = Payment * {(1+r)n – 1}/r
$120,000 = P * (1.015512 – 1)/0.0155
$120,000 = P* 13.0777
P = $120,000/13.0777 = $9,175.93
Amount to be deposited each period = $9,175.93
2.
Period
Sinking fund beginning balance
Interest @ 0.0155
Sinking fund ending balance
1
$9,175.93
$142.23
$9,318.16
2
$18,494.09
$286.66
$18,780.75
3
$27,956.68
$433.33
$28,390.00
4
$37,565.93
$582.27
$38,148.21
5
$47,324.14
$733.52
$48,057.66
6
$57,233.59
$887.12
$58,120.71
7
$67,296.64
$1,043.10
$68,339.74
8
$77,515.67
$1,201.49
$78,717.16
9
$87,893.09
$1,362.34
$89,255.43
10
$98,431.36
$1,525.69
$99,957.05
11
$1,09,132.98
$1,691.56
$1,10,824.54
12
$1,20,000.47
From the table, annual payment and interest accrued can be tabulated as below:
Year
Payments
Interest accrued
1
$36,703.72
$1,444.49
2
$36,703.72
$3,865.24
3
$36,703.72
$4,579.59
Period
Sinking fund beginning balance
Interest @ 0.0155
Sinking fund ending balance
1
$9,175.93
$142.23
$9,318.16
2
$18,494.09
$286.66
$18,780.75
3
$27,956.68
$433.33
$28,390.00
4
$37,565.93
$582.27
$38,148.21
5
$47,324.14
$733.52
$48,057.66
6
$57,233.59
$887.12
$58,120.71
7
$67,296.64
$1,043.10
$68,339.74
8
$77,515.67
$1,201.49
$78,717.16
9
$87,893.09
$1,362.34
$89,255.43
10
$98,431.36
$1,525.69
$99,957.05
11
$1,09,132.98
$1,691.56
$1,10,824.54
12
$1,20,000.47
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.