Deck 9: Exploring Financial Tools and Functions

Question 1

What is the formula to calculate how much a savings account would be worth if the initial balance is $500 with monthly deposits of $50 for 10 years at 5.8 percent annual interest compounded monthly What is the formula result

Answer 1

Future Value of Loan
To calculate the future worth of the savings account the FV function is used. Screenshot displaying FV function is as shown:
Syntax of future value of loan are as follows:
= FV( rate, nper, pmt, [,pv=0], [,type=0])
Here, rate is the attribute of FV function which represents interest rate. nper represents the total number of payment period. pmt represents the amount which is paid per period. pv is the attribute of PMT function which helps in representing the present period and type represent payments is due or not.
To calculate future value of loan:
1. For the given situation, period is taken as one month since interest is compounded monthly. Hence rate equals 5.8% divided by 12.
2. Payments are made over 10 years and hence number of payments is equal to 10 * 12, i.e. 120 months.
3. The amount deposited every month is $50. The present value is $500.
4. Putting these values in the FV function values get is as follows: FV( 5.8%/12, 120, 50, 500, 0) = $8,997.41
Screenshot displaying FV function after calculation is as shown:

Question 2

You receive $50 at the end of Year 1 from an investment, $75 at the end of Year 2, and $100 at the end of Year 3. If the rate of return is 6 percent, what is the present value of this investment Show the formula and formula results you used to answer this question.

Answer 2

Net Present Value
Net present value is used to determine and compare the profitability in the investment. Net present value helps in adjusting expenditures and returns so that it can be evaluated equally over time. Net present value is calculated by using following formula:
In Excel Net present value is calculated by using NPV function. The net present value of an investment is calculated using the NPV function. Screenshot displaying NPV function is as shown:
Syntax of NPV function is as follow:
=NPV(rate, value1, [,value2], [,value3],...)
Here, rate is the attribute of NPV function which represents interest rate. value is the return from the investment over period. value1 represents the return after period one and value2 represents the return after period two and so on.
Here, rate is taken to be 6%. value1 is taken as $50, value2 is taken as $75 and value3 is taken as $100. Putting these values in NPV function values which user get is as follows:
NPV(6%, 50, 75, 100) = $197.88
Screenshot displaying NPV function after calculation is as shown:
The net present value of this investment is $197.88 from today.

Question 3

You want a savings account to grow from $1,000 to $3,000 within two years. Assume the bank provides a 5.2 percent annual interest rate compounded monthly. What is the formula to calculate how much you must deposit each month to meet your savings goal What is the formula result

Answer 3

Amount Deposited Per Period
To calculate the amount to be deposited every period, PMT function is used. Screenshot displaying PMT function is as follows:
Syntax of present value of loan is as follows:
= PMT(rate, nper, [,pv=0], [,fv=0], [,type=0])
Here, rate is the attribute of PMT function which represents interest rate. pmt represents the amount which is paid per period. pv is the attribute of PMT function which helps in representing the present period and type represent payments is due or not. type=0 represents payments is made at the beginning.
To calculate Amount Deposited Per Period:
1. Period is taken as one month. Therefore the rate becomes 5.2%/12.
2. The total number of payments is 2*12, i.e. 24.
3. The present value is taken as $1000 and future value is taken as $3000.
4. Type is taken as 0.
5. Putting these values in the PMT function values get is as follows: PMT( 5.2%/12, 24, 1000, 3000, 0) = $162.84
Screenshot displaying PMT function after calculation is as shown:

Question 4

You spend $350 on an investment that pays $75 per year for the next six years. If you make the investment immediately, what is the net present value of the investment Assume a 6 percent rate of return. Show the formula and formula results you used to answer this question.

Question 5

You want to take out a loan for $200,000 at 7 percent interest compounded monthly. If you can afford to make monthly payments of only $1,500 on the loan, what is the formula to calculate the number of months required to repay the loan completely What is the formula result

Question 6

Suppose that instead of spending $350 immediately on an investment, you spend $350 one year from now and then receive $75 per year for the next six years after that. What is the net present value assuming a 6 percent rate of return Show the formula and formula results you used to answer this question.

Question 7

Rerun your calculations from Question 4 assuming that you can afford only a $1,000 monthly payment. What are the revised formula and resulting value How do you explain the result

Question 8

Calculate the internal rate of return for the investment in Question 4. If another investment is available that pays a 7.3 percent rate of return, should you take it Show the formula and formula results you used to answer this question.

Question 9

You take out a 10-year loan for $150,000 at 6.3 percent interest compounded monthly. What are the formula to calculate the monthly payment and the resulting value

Question 10

What do red tracer arrows indicate

Question 11

For the loan conditions specified in Question 6, what are the formulas to calculate the amount of the first payment used for interest and the amount of the first payment used to repay the principal What are the resulting values

Question 12

What is the purpose of the Watch Window

Question 13

For the loan conditions specified in Question 6, what are the formulas to calculate how much interest you will pay in the first year and how much you will repay toward the principal What are the resulting values

Question 14

The first value in a linear trend is 1000 and the fifth value is 4000. What are the values of the second, third, and fourth items

Question 15

The first value in a growth trend is 1000 and the fifth value is 4000. What are the values of the second, third, and fourth items

Question 16

The first value in a series is 1000. Extrapolate the next four values assuming a linear trend with a step size of 500.

Question 17

The first value in a series is 1000. Extrapolate the next four values assuming a growth trend step size of 15 percent.

Question 18

A new business buys $25,000 worth of computer equipment. If the useful life of the equipment is five years with a salvage value of $2,000, what is the formula to determine the depreciation during the first year assuming straight-line depreciation What is the formula result

Question 19

Assume a declining balance depreciation for the computer equipment described in Question 5. What are the formula and the result

Question 20

Write the formula to calculate how much the computer equipment described in Question 5 would depreciate in the first year assuming double-declining balance depreciation with a factor of 2. What is the result

Question 21

If you take out a loan for $200,000 that must be repaid in 10 years with quarterly payments of $7,200, what is the formula to calculate the annual interest rate of the loan What is the result

Question 22

Explain the difference between positive and negative cash flow. If you borrow $10,000 from a bank, is that a positive or negative cash flow Justify your answer.

Question 23

If the annual rate of return is 5 percent, is $95 today worth more than, less than, or the same as $100 a year from now Show the formula and formula results you used to answer this question.