spread sheets use as examples the first two and fill out last one

Complete the following problems. For assistance, you may want to refer to these examples. The Word document has instructions on using the Excel spreadsheet.

Week 04 Example Problems

Week 04 Example Problems.xls

Required:Download the

Week 04 Problems Excel spreadsheet

to use in completing your problems. You will notice that each problem has its own worksheet.

A self-employed person deposits $1,250 annually in a retirement account (called a SEP-IRA) that earns 5.5%.
a.   How much will be in the account at age 62 if the savings program starts when the individual is age 50?

b.   How much additional money will be in the account if the saver defers retirement until age 66 and continues the contributions until then?

c.    How much additional money will be in the account if the saver discontinues the contributions at age 62, but lets it build up until retirement at age 66?

2. If a firm has $250,000 to invest and can earn 8.5%, compounded annually, how much will the firm have after two years?

3.   A father has decided to set aside a one time lump sum for college that will amount to $60,000 by the time his 5 year old is 18 years old (13 years). Use 8% as the rate. Figure the dollar amount to put in the fund assuming no further investments will be made. How much must he invest right now to amount to $60,000 in 13 years?

4.    You win a judgment in an auto accident for $275,000. You will immediately receive $135,000 in cash, but must pay your lawyer’s fee of $91,666 out of that sum. In addition you will receive $5,500 per year for 20 years for a total of $110,000 after which the balance owed of $30,000 will be paid. If the interest rate is 7 percent, what is the current value of your settlement?

5.     A firm borrows $935,000 for 7 years for a large item of equipment and installation costs. The interest rate is 7.5%. The loan requires that the interest and principal be paid in equal, annual payments that cover the interest and principal. The interest is determined on the declining balance that is owed. What are the annual payments and the amount by which the loan is reduced during the first year?

6.     A company leases equipment for seven years. The equipment costs $28,000 and the owner (called the “lessor”) wants to earn 9.5% on the lease. What should be the lease payments?

Week

4 Example Problems

Present Value and Future Value using Excel Functions

Please download the Week 04 Example Problems Excel spreadsheet to solve the following:

Present Value

To calculate present value, we will use the built-in PV feature in Excel.

Following are the values that we will use in the Example problem on the Present Value sheet:

Rate (Interest rate)

Nper (Number of periods)

PMT (Payment)

FV (Future value)

$100

Type

Click on the PV cell to see how the formula was entered. You may notice that the solution came up as a negative number, this is because cash flows out are recognized as negatives. This is easy to correct if you would like, simply enter a negative before the FV while entering the formula.

Compare this to this week’s material that demonstrates this concept using a financial calculator All you need to know to “translate” those directions is that N, number of periods is Nper, also meaning number of periods in Excel and i, interest rate, is Rate, meaning interest rate, in Excel. The rest is precisely the same. Keep PMT and Type zero for the PV and FV problems.

Try using this same formula to calculate the new balance for Example 1A and 1B. There will be a note next to the cell that will inform you if the answer is “correct” or “incorrect.” Keep working the problems until you have the correct answer.

Future Value

To calculate future value, we will use the built-in FV feature in Excel.

Following are the values that we will use in the Example problem on the Future Value sheet:

Nper (Number of periods)

PMT (Payment)

	Rate (Interest rate)	5%
20
PV (Present value)	$-100
Type

We use the FV function in Excel to solve this problem. Click on the FV cell to see how the formula was entered.

Try using this same formula to calculate the new balance for Example 2A and 2B. There will be a note next to the cell that will inform you if the answer is “correct” or “incorrect.” Keep working the problems until you have the correct answer.

Interest

Click on the Interest tab to complete the following examples:

Simple Interest

In the Example 1 problem, there is $100 of principal, and an annual interest rate of 5%. To calculate the ending balance, first we calculate the interest (principal x rate x time), then add it to the principal. We now have a new balance of $105. If you click in the cells, you can see the formulas that were used.

Try using this same formula to calculate the new balance for Example 3A and 3B. There will be a note next to the cell that will inform you if the answer is “correct” or “incorrect.” Keep working the problems until you have the correct answer.

Compound Interest

The text explains these problems using the future value tables. In our practice activities, we will use the Power function in Excel. Powers are quick ways to multiply repeatedly rather than calculating the interest 20 times.

In Example 2, we will calculate the future value of $100 that is invested for 20 years at 5%, compounded annually.

To calculate using this model:

1. We first figure 1 + interest rate (1.05)

2. Then calculate the Power at 20 periods (2.653298) (Use the POWER function)

3. To figure the end result, we multiply the $100 (present value) X the Power result (2.653298), to end up with $265.33.

As an alternate model, we could use the Future Value function built into Excel. In Example 3, this method has been utilized.

1. Insert the function, FV, the rate is 5%

2. Nper is the 20 periods

3. PV is the $100

4. Leave the other areas blank for this example

You will see that the same answer appears as in Example 1. One thing that you may notice is that the answer appears as a negative because it is an outflow of cash. To change this, simply enter a negative before the $100.

Try using this same formula to calculate the new balance for Example 4A and 4B. There will be a note next to the cell that will inform you if the answer is “correct” or “incorrect.” Keep working the problems until you have the correct answer.

Annuities

Present Value of an Annuity

Click on the PV Annuity worksheet, in the Example you are expecting to receive $100 payments at the end of each year for three years and the rate is 6% on invested funds. We use the same PV formula, but add more information. Click on the PV cell to see how the formula was entered. The difference between an ordinary annuity and an annuity due is when the payment is made. If the payment is made at the beginning of the period, it is an annuity due. If made at the end of the period, it is an ordinary annuity. The only change in your Excel formula is that you change the type to a “1” rather than a “0”.

Try using this same formula to calculate the new balance for Example 5A and 5B. There will be a note next to the cell that will inform you if the answer is “correct” or “incorrect.” Keep working the problems until you have the correct answer.

Future Value of an Annuity

Click on the FV Annuity worksheet, in the Example you trying to calculate how much will be in your savings account if you deposit $100 payments at the end of each year for three years and the rate is 5% on savings. We use the same FV formula, but add more information. Click on the FV cell to see how the formula was entered.

Try using this same formula to calculate the new balance for Example 6A and 6B. There will be a note next to the cell that will inform you if the answer is “correct” or “incorrect.” Keep working the problems until you have the correct answer.

Adapted from:

Mayo, H. (2007). Basic finance: An introduction to financial institutions, investments & management. United States: Thomson South-Western.

Present Value

)

Rate

Nper 5
PMT 0

Type 0

Rate

Nper

PMT 0

Type 0

PV Incorrect


		Example
									Rate	6%
				Nper				5
				PMT							0
				FV	$				1
			Type
							PV	($74.7	3
	Example 1
		5%
$	15
						Incorrect	($117.53)
Example 1B
0.07
	10
250
($127.09)

Future Value

Example

Rate

Nper

PMT 0

Type 0

Rate 5%
Nper 10
PMT 0
PV -$100.00
Type 0

FV Incorrect

Rate

Nper 15

PMT 0

Type 0

FV Incorrect

			5.00%
				20
	–		$100.00
		$265.33
	Example 2
162.8894626777
Example 2B
	7%
-$250.00
689.7578851788

Interest

using product formula.

Example 1

$100.00

Rate 5%

Interest

Principal

Rate

Interest
New balance Incorrect
Principal

Rate

Interest
New balance Incorrect

Example 2
Interest 5.00%

PV $100.00

2.6532977051

$265.33

Present Value $100.00
Rate 5.00%

Future Value $265.33
interest

plus 1 1

1 plus interest

Power 20

POWER function
PV

(1 + i)n Power Function
FV

Incorrect

Present Value $500.00
Rate 4.00%

Term 20

Future Value Incorrect

interest 5.00%

plus 1 1
1 plus interest
Power 20
POWER function

(1 + i)n Power Function
FV result Incorrect

Present Value $200.00

Rate 5.00%
Term 20

Future Value Incorrect

Simple		interest
		Principal
$5.00
		New balance	$105.00
Sample 3A
$1,000.00
6.00%
Sample 3B
$2,500.00
7.50%
Compound interest using power formula and FV Function
		plus 1
		1 plus interest	1.05
		Power
		POWER function		2.6532977051
		(1 + i)n			Power Function
Result
Example 3
		Term
Example 4A
		4.00%
	$500.00
	result
1095.5615715167
Example 4B
	$200.00
530.6595410289

PV of Annuity

Example

Rate 6%
Nper 3
PMT

FV 0

Rate 7%
Nper 5

PMT ($100)
FV 0
type 0

PV Incorrect

Rate 4.00%
Nper 10
PMT

FV 0
type 0

PV Incorrect

Present Value of an Annuity
	($100)
		type
$267.30
Example 5A
410.0197435948
Example 5B
($50.00)
405.5447889678

FV of Annuity

Example

Rate 5%

Nper 3
PMT ($100)

PV 0

type 0

Rate 6%
Nper 5
PMT ($100)
PV 0
type 0

Incorrect

Rate

Nper 10

PMT

PV 0
type 0

FV of an ordinary annuity Incorrect

Future Value of an Annuity
FV of annuity	$315.25
Example 6A
	FV of an ordinary annuity	$563.71
Example 6B
4%
($50)
$600.31

Problem 1

Rate
Nper
PMT
PV
Type
FV

Graded problem #1

1a.

Rate

Nper

PMT

Type

1b.

C18 minus C9

Additional money saved if the contributions continue until age 66

The first part is a repeat of 1a.

1c continued

C40 minus C31

Additional money saved if contributions stop at age 62, but the money keeps growing until age 66.

Problem 2

Rate
Nper
PMT
PV
Type
FV

Problem 3

Rate
Nper
PMT
FV
Type
PV

Example

Problem 4

Rate Rate
Nper Nper
PMT PMT
FV FV
Type Type
PV PV

Rate Rate
Nper Nper
PMT PMT

FV FV

Type Type
PV PV

Problem 4.
Example problem from #21
Problem 4 continued	Example continued
`
Now add C9, C18, plus $135,000 minus $91,666	Add H9, H18, $25,000 and subtract $15,000
Example answer
The text answer at the back is $10 different due to rounding difference in their table method.
Settlement Value

Problem 5

Rate
Nper
PV
FV
Type

PMT

Rate
Nper

PMT

the first year

Problem 5.
Yearly payment owed
How much principal is reduced	the first year
Principal
first year interest
Principal paid

Problem 6

Rate
Nper
PV
FV
Type
PMT

Example from text

Turn in your highest-quality paper
Get a qualified writer to help you with

“ spread sheets use as examples the first two and fill out last one ”

Get high-quality paper

Guarantee! All work is written by expert writers!

Still stressed from student homework?

Get quality assistance from academic writers!

Order now