Question

In: Accounting

Create a table on a spreadsheet with columns labelled “Count”, “Date”, “Interest this Period”, and “Total”....

Create a table on a spreadsheet with columns labelled “Count”, “Date”, “Interest this Period”, and “Total”. As depicted below.



You will begin with the count at 0, and the date of Sept 30, 2016. For that first row, the “Interest this period” is zero, and the total is $39,000 CAD. Above the columns, put the label “rate”, and in the cell below that, enter your periodically compounding rate of 20%. Put the label “Timestep in years” and in the cell below that, enter =1/4.

For each subsequent row of the table, use formulas to set the count equal to one more than the previous count, and set the date equal to the previous date plus 365*[the value in the timestep cell]. The “Interest this Period” is the “Total” from the previous “Date”, times the “rate” cell, times the “timestep” cell. The “Total” for each row is the “Total” from the previous row plus the “Interest this period”. Make sure that you use dollar signs for absolute addresses where appropriate in order to be able to copy these formulas to later rows. Copy that new row, and paste it into the rows below it so that the count goes to at least 100.

You can validate your spreadsheet by checking that the total comes out right if the interest rate and initial total from a previous question is used with a timestep of 1. Don’t forget to restore the timestep and rate to the values for this problem when done.
(a) What is the date that Excel shows for the row whose count is 100? (Copy and paste the content of that cell into the answer box below.)
(b) What is the interest earned for the period with count 72?

Solutions

Expert Solution

Hi

Pls read below for understanding the snips :

Snip 1: Solved as per the question and the comments to the respective cells have been taken from the question for your understanding as to why it has been done like that.

Snip 2: I copied and pasted the answer from the excel sheet I worked on so that you can simply copy and paste the same to your excel.

Snip 3: This is the snip that shows the formulas that have been used in excel. This is for your understanding.

Part (a) What is the date that Excel shows for the row whose count is 100? (Copy and paste the content of that cell into the answer box below.

Count Date Interest this period Total (CAD $)
100 24-Sep-41                244,217       5,128,549

See snip 2 for detailed calculations

Part (b) What is the interest earned for the period with count 72?

Count Date Interest this period Total (CAD $)
72 26-Sep-34                  62,298       1,308,260

See snip 2 for detailed calculations

Snip 1: Solved as per the question and the comments to the respective cells have been taken from the question for your understanding as to why it has been done like that.

Snip 2: I copied and pasted the answer from the excel sheet I worked on so that you can simply copy and paste the same to your excel.

Rate Timestep in years
20% 0.25 (entered as =1/4)
Count Date Interest this period Total (CAD $)

0

30-Sep-16

                        -  

           39,000

1

30-Dec-16

1,950

           40,950

2

31-Mar-17

                  2,048

           42,998

3

30-Jun-17

                  2,150

           45,147

4

30-Sep-17

                  2,257

           47,405

5

30-Dec-17

                  2,370

           49,775

6

31-Mar-18

                  2,489

           52,264

7

30-Jun-18

                  2,613

           54,877

8

30-Sep-18

                  2,744

           57,621

9

30-Dec-18

                  2,881

           60,502

10

31-Mar-19

                  3,025

           63,527

11

30-Jun-19

                  3,176

           66,703

12

30-Sep-19

                  3,335

           70,038

13

30-Dec-19

                  3,502

           73,540

14

30-Mar-20

                  3,677

           77,217

15

29-Jun-20

                  3,861

           81,078

16

29-Sep-20

                  4,054

           85,132

17

29-Dec-20

                  4,257

           89,389

18

30-Mar-21

                  4,469

           93,858

19

29-Jun-21

                  4,693

           98,551

20

29-Sep-21

                  4,928

         103,479

21

29-Dec-21

                  5,174

         108,653

22

30-Mar-22

                  5,433

         114,085

23

29-Jun-22

                  5,704

         119,789

24

29-Sep-22

                  5,989

         125,779

25

29-Dec-22

                  6,289

         132,068

26

30-Mar-23

                  6,603

         138,671

27

29-Jun-23

                  6,934

         145,605

28

29-Sep-23

                  7,280

         152,885

29

29-Dec-23

                  7,644

         160,529

30

29-Mar-24

                  8,026

         168,556

31

28-Jun-24

                  8,428

         176,984

32

28-Sep-24

                  8,849

         185,833

33

28-Dec-24

                  9,292

         195,124

34

29-Mar-25

                  9,756

         204,881

35

28-Jun-25

                 10,244

         215,125

36

28-Sep-25

                 10,756

         225,881

37

28-Dec-25

                 11,294

         237,175

38

29-Mar-26

                 11,859

         249,034

39

28-Jun-26

                 12,452

         261,485

40

28-Sep-26

                 13,074

         274,560

41

28-Dec-26

                 13,728

         288,288

42

29-Mar-27

                 14,414

         302,702

43

28-Jun-27

                 15,135

         317,837

44

28-Sep-27

                 15,892

         333,729

45

28-Dec-27

                 16,686

         350,415

46

28-Mar-28

                 17,521

         367,936

47

27-Jun-28

                 18,397

         386,333

48

27-Sep-28

                 19,317

         405,650

49

27-Dec-28

                 20,282

         425,932

50

28-Mar-29

                 21,297

         447,229

51

27-Jun-29

                 22,361

         469,590

52

27-Sep-29

                 23,480

         493,070

53

27-Dec-29

                 24,653

         517,723

54

28-Mar-30

                 25,886

         543,609

55

27-Jun-30

                 27,180

         570,790

56

27-Sep-30

                 28,539

         599,329

57

27-Dec-30

                 29,966

         629,296

58

28-Mar-31

                 31,465

         660,760

59

27-Jun-31

                 33,038

         693,798

60

27-Sep-31

                 34,690

         728,488

61

27-Dec-31

                 36,424

         764,913

62

27-Mar-32

                 38,246

         803,158

63

26-Jun-32

                 40,158

         843,316

64

26-Sep-32

                 42,166

         885,482

65

26-Dec-32

                 44,274

         929,756

66

27-Mar-33

                 46,488

         976,244

67

26-Jun-33

                 48,812

      1,025,056

68

26-Sep-33

                 51,253

      1,076,309

69

26-Dec-33

                 53,815

      1,130,124

70

27-Mar-34

                 56,506

      1,186,631

71

26-Jun-34

                 59,332

      1,245,962

72

26-Sep-34

                 62,298

      1,308,260

73

26-Dec-34

                 65,413

      1,373,673

74

27-Mar-35

                 68,684

      1,442,357

75

26-Jun-35

                 72,118

      1,514,475

76

26-Sep-35

                 75,724

      1,590,198

77

26-Dec-35

                 79,510

      1,669,708

78

26-Mar-36

                 83,485

      1,753,194

79

25-Jun-36

                 87,660

      1,840,854

80

25-Sep-36

                 92,043

      1,932,896

81

25-Dec-36

                 96,645

      2,029,541

82

26-Mar-37

               101,477

      2,131,018

83

25-Jun-37

               106,551

      2,237,569

84

25-Sep-37

               111,878

      2,349,447

85

25-Dec-37

               117,472

      2,466,920

86

26-Mar-38

               123,346

      2,590,266

87

25-Jun-38

               129,513

      2,719,779

88

25-Sep-38

               135,989

      2,855,768

89

25-Dec-38

               142,788

      2,998,556

90

26-Mar-39

               149,928

      3,148,484

91

25-Jun-39

               157,424

      3,305,908

92

25-Sep-39

               165,295

      3,471,204

93

25-Dec-39

               173,560

      3,644,764

94

25-Mar-40

               182,238

      3,827,002

95

24-Jun-40

               191,350

      4,018,352

96

24-Sep-40

               200,918

      4,219,270

97

24-Dec-40

               210,964

      4,430,234

98

25-Mar-41

               221,512

      4,651,745

99

24-Jun-41

               232,587

      4,884,332

100

24-Sep-41

               244,217

      5,128,549

Snip 3: This is the snip that shows the formulas that have been used in excel. This is for your understanding.

ekkarill92 answered 1 year ago
Next > < Previous

Related Solutions

Create a table on a spreadsheet with columns labelled “Count”, “Date”, “Interest this Period”, and “Total”....
Create a table on a spreadsheet with columns labelled “Count”, “Date”, “Interest this Period”, and “Total”. As depicted below. 1    timesteps in years rate.    2 <enter timestep> <enter your rate> 3 count date interest this period total$ 4 0 30-Sep-16 <enter your total> 5 1 You will begin with the count at 0, and the date of Sept 30, 2016. For that first row, the “Interest this period” is zero, and the total is $22,000 CAD. Above the...
Create a table on a spreadsheet with columns labelled “Count”, “Date”, “Interest this Period”, and “Total”....
Create a table on a spreadsheet with columns labelled “Count”, “Date”, “Interest this Period”, and “Total”. As depicted below. You will begin with the count at 0, and the date of Sept 30, 2016. For that first row, the “Interest this period” is zero, and the total is $22,000 CAD. Above the columns, put the label “rate”, and in the cell below that, enter your periodically compounding rate of 19%. Put the label “Timestep in years” and in the cell...
Which of the following accounts would appear in the Income Statement columns of the end-of-period spreadsheet?...
Which of the following accounts would appear in the Income Statement columns of the end-of-period spreadsheet? Select one: a. Unearned Revenue b. Cash c. Prepaid Insurance d. Net Loss
The following accounts were taken from the Adjusted Trial Balance columns of the end-of-period spreadsheet for...
The following accounts were taken from the Adjusted Trial Balance columns of the end-of-period spreadsheet for April 30, for Finnegan Co.: Accumulated Depreciation $32,000 Fees Earned 78,000 Depreciation Expense 7,250 Rent Expense 34,000 Prepaid Insurance 6,000 Supplies 400 Supplies Expense 1,800 ​ Prepare an income statement.
The following data were taken from the Adjusted Trial Balance columns of the end-of-period spreadsheet for...
The following data were taken from the Adjusted Trial Balance columns of the end-of-period spreadsheet for April 30 for Abigail Company: Accumulated Depreciation $42,400 Prepaid Rent 6,800 Supplies 850 Unearned Fees 7,310 Trucks 49,300 Cash 3,400 Common Stock 2,000 Retained Earnings ? ​ Prepare a classified balance sheet.
Create a table showing the payment of a mortgage of $239,000 month by month. Create columns...
Create a table showing the payment of a mortgage of $239,000 month by month. Create columns for: - Time (in years) - Interest (for that month) - Payment (always the same value) - Payment against principal - Remaining Principal The mortgage is to last 33 years, and the nominal interest rate is 6.03% (a) What is the monthly payment? (b) What is the interest paid at the end of the 9th month? (c) How much of the principal is paid...
1. Write the statements to create a table named REQUIREMENTS. The table has the following columns:...
1. Write the statements to create a table named REQUIREMENTS. The table has the following columns: credits number (primary key) and degree name. 2. Write the statements to create a table named CANDIDATE with the following columns names. Pick the best column type: student_id, first name, last name, credits and graduation date. The credits column is a foreign key to the credits column in the REQUIREMENTS table. 3. Write the statement(s) to Insert 2 rows in your REQUIREMENTS table. Make...
Create a spreadsheet to calculate your projected total costs, total revenues, and total profits for giving...
Create a spreadsheet to calculate your projected total costs, total revenues, and total profits for giving a seminar on cost estimating. Make the following assumptions: You will charge $995 per person for a two-day class. You estimate that 30 people will attend the class, but you should keep an option to change this input. Your fixed costs include $500 total to rent a room for both days, setup fees of $500 for registration, and $300 for designing a postcard for...
Create a table with two columns. Name the table First Initial _ Last Name (e.g. John...
Create a table with two columns. Name the table First Initial _ Last Name (e.g. John Dow will create table j_dow). You have to audit all DML statements on your table. To do this you write two triggers: 1. To log any DML statements that users might run on this table. The results must be stored in the First Initial _ Last Name _ Log table (e.g. John Dow will create table j_dow_log). The table should have unique event ID,...
Exercise 2: Create a spreadsheet to calculate your projected total costs, total revenues, and total profits...
Exercise 2: Create a spreadsheet to calculate your projected total costs, total revenues, and total profits for giving a seminar on cost estimating. Make the following assumptions: You will charge $600 per person for a two-day class. You estimate that 30 people will attend the class, but you want to change this input. Your fixed costs include $500 total to rent a room for both days, setup fees of $400 for registration, and $300 for designing a postcard for advertising....
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT