Using the template you have downloaded and the prototype figure below, construct a savings plan spreadsheet using three different methods (iteration (steps), formula, and Excel function) that will arrive at the very same balance if properly done. Be sure to type in the Givens box the same principal, compound, and APR as the prototype figure. From Month #18 (row 26) and thereafter, you will be building formulas that are flexible enough to accommodate other values you type into the Givens box later.Givens:

Month

Line Number

Principal

Payment

APR

Compounds

7

485.00

78.00

4.20%

Principal

Interest

0

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

Payment

Total

Methods

Month #18 Iterations (Steps)

“=

Month #18 Formula (Regular Payments)

“=

Month #18 Excel Function

“=

Total:

Reflection 1: What would be another way to calculate total interest rather than merely using ‘=sum(‘?

Reflection 2: When was the original principal invested and when was the payment invested (end or

beginning of the period)? In the finance world, what type of investment is this called?

Reflection 3: What are some of the pros and cons to each of the three methods you have used?

Reflection 4: Which of the three methods is your favorite way to calculate the balance and why?

Reflection 5: What impressions or insights have you gained from this exercise?

Regular Payments Savings Plan

A Three Methods Approach

Before you begin:

• Look for your name on the sign-up sheet (given by your instructor) and copy the Line #,

Principal, and APR.

• Review the savings plan formula (regular payments) from section 4-C of your text.

• Remember that all formulas in an Excel spreadsheet begin with an equality

symbol ‘=’.

• Recall that to reference a cell, you can click on it or type its column letter and row

number.

Download Spreadsheet:

• Download or ask your instructor for the file “Savings_Plan_Template.xls”

which provides you a spreadsheet framework. This helps you concentrate on

the three methods of calculating the lump sum investment without worrying

about the formatting details.

Procedure: Using the template you have downloaded and the prototype figure below,

construct a savings plan spreadsheet using three different methods (iteration (steps),

formula, and Excel function) that will arrive at the very same balance if properly done.

Be sure to type in the Givens box the same principal, compound, and APR as the

prototype figure. From Month #18 (row 26) and thereafter, you will be building

formulas that are flexible enough to accommodate other values you type into the

Givens box later.

Iterations (Steps) Method

1. Link by a cell reference, the

total cell of Month #0 (cell F8)

to the principal in the Givens

box (cell D2).

2. Now let principal from Month

#1 (cell C9) reference from the

total (cell F8).

3. For the interest in Month #1

(cell D9), create a formula by

multiplying the principal (cell

C9) by the given APR (cell

D4) divided by the given

Compounds (cell D5). Note:

because you will want to

always use the same given

APR and compound values

even after you copy or fill the

formulas down the columns,

you must use absolute cell

referencing for those cell locations (locking them in). This can be done by

pressing F4 (for Windows) or Command-T (for Macintosh) while the cursor is in

the middle or at the end of cell references D4 and D5.

4. Now add the interest to the principal (that is, compound the interest) along

with the payment from Month #1 (cell E9) to make a new total balance (cell

F9). This is done by adding cells C9, D9, and E9 as a formula (that is,

‘=C9+D9+E9’ or ‘=sum(C9:E9)’).

5. Complete the Iteration Method by dragging over the principal, interest, payment,

and total cells from Month #1 and filling their corresponding formulas down to

Month #36. This can also be done by looking for the small black cross hair in the

bottom right corner of your shaded rectangle and dragging the three cells down or

simply double-clicking. See your software help or instructor for help. Don’t forget

to check for accuracy.

Formula Method

1. In the Formula box at the bottom of your template, follow the directions for the

Savings Plan (Regular Payments) formula as shown in the prototype.

2. Confirm that your answer matches the same one found for Month #18 using

iterations.

Excel Function Method

1. In the Excel Function box at the bottom of your template, follow the directions

for the formula as shown in the prototype. The focus here is to learn how to use

the built-in function known as future value (FV) for Excel. You can find helpful

dialog boxes to guide you through this process by looking for the “Insert” ribbon

in the newer Office suite for Windows or by using the “Insert” pull-down menu

and finding the feature “Function…” in the Macintosh Office suite.

2. You will notice that the template and prototype indicate cell references in the

future value function rather than actual values. This reinforces the idea of

keeping your formula generalized to accommodate future changes to the

Givens box.

3. Be aware of the need to place a negative sign after the equality to keep the future

value as a positive value.

Polishing the Worksheet

Be sure to total your interest after Month #36 (D45) and payments (E45) by using

the function ‘Sum’ and dragging over the interest cells from Month #1 to Month

#36 or take advantage of the Auto Sum tool that may be on one of your tool bars.

Check to see if the very last balance (Month #36) matches the prototype quantity.

Results Summary: Upon verifying that your compound interest spreadsheet

(based on the prototype givens) has correct quantities throughout, modify your

spreadsheet according to the sign-up sheet values you were given. Then answer

the 5 reflection questions in the textboxes on the spreadsheet. Turn in your

completed spreadsheet on Canvas.

Purchase answer to see full

attachment

#### Why Choose Us

- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee

#### How it Works

- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "
**PAPER DETAILS**" section. - Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “
**CREATE ACCOUNT & SIGN IN**” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page. - From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.