Engineering Economy

Using Spreadsheet Functions- Excel Financial

Functions:

Present Value, P:=PV(i%,n,A,F)

Future Value, F=FV(i%,n,A,P)

Equal, periodic value, A: =PMT(i%,n,P,F)

Number of periods, n: =NPER(i%,A,P,F)

Compound interest rate, i: =RATE(n,A,P,F)

Compound interest rate, i: =IRR(first_cell:last_cell)

Present value, any series, P: =NPV(i%,second_cell:last_cell) + first_cell

*Upload the excel file or a screenshot of the excel spreadsheet including the functions that were used to solve the parts of the problem

1. A girl is currently in 3rd grade. When she was born,

a college savings plan was open and currently have $5000 in her account.

Assuming she starts college after she graduates from high school,

how much must I put in her account each year, including the 4 years

she is in college, such that $0 remain in her account upon graduation:

a) What will be the cost of education (tuition only) when she is ready to start college? Lets

make the following assumptions. The current tuition for college in a public institution is

$6,000. It is increasing at a rate of 6% per year. What will the cost be in each of the 4 years

she is in college?

b) Create the cash flow in Excel, starting with the $5000 currently in the account, and noting the

annual installments I will make starting at the end of year 1 through her last year in college,

and note the cash outflows from the account for the 4 years she is in college.

c) Determine the annual installments that I must put into her account such that $0 will remain in

her account upon graduation. Assume a 6% interest rate.

d) What are the annual installments if college tuition increases at 9% per year?

e) What are the annual installments if college tuition increases 9% per year, but the college

savings plan returns only 5% per year?