Engineering economy using Excel to solve problems

on Excel Function NPV. NPV function does not include cash flows for time period
zero. You must add any cash flows from time period zero to the results of NPV in order to get
total Present Worth. Include all years. Don’t leave blanks (use 0s if there is no cash flow for th

• A company is purchasing an existing warehouse and land for $4.2 million and will pay off the land in 15 equal annual year-end payments.
  1. If the interest rate is 5%, how much is each payment.
  2. If the interest rate is 9%, how much is each payment.

What is the monthly payment for a 3 year new car loan, when the nominal annual interest is 6%.  After the down payment and other up-front charges, the amount borrowed is $42,000.  Show your calculations and results using two methods:

• Use Engineering Economy factors and the compound interest tables found in Appendix C.
• Using spreadsheet. Set up table with known values and use Excel payment function (PMT).

A project has a first cost of $10,000, net annual benefits of $2,000, and a salvage value of $3,000 at the end of its 10-year useful life. The project will be replaced identically at the end of 10 years, and again at the end of 20 years. What is the present worth of the entire 30 years of service, given an interest rate of 10%?

N = 10, I/Y = 10%, PMT = 2,000, FV = 3,000

=> Compute PV = $13,445.76

Present Worth (PW) = 13,445.76 – 10,000 = $3,445.76

Now, for the entire 30 year cycle,

PW = PW1 + PW2 / (1+r)^10 + PW3 / (1 + r)^20

= 3,445.76 + 3,445.76 / 1.1^10 + 3,445.76 / 1.1^20

= $5,286.45

Cathy, a graduating civil engineering student, is
offered a job at a remote construction project over-
seas. If Cathy is still with the project when it is com-
pleted at the end of year 5, she will receive a bonus
of $100,000. Her discount rate rs l27o. What is the
present worth of the bonus?
(Answer: $56,740)

• (2 pts) Determine the down payment that must be made on an automobile that cost $28,000 if you want to make monthly payments of $500 for 48 months if the annual (nominal) interest rate is 6%.

Sample Answer need to solve in the excel

Find the present value of payment scheme at 8%/12 = 0.6667%

PV = A(P/A, r, N) = 400*(P/A, 0.6667%, 48) = 400*40.961913 = $16,385

Since the cost is $26,000, the downpayment is $26,000 – $16,385 = $9,615

(2 pt) Determine what your monthly loan payment would be if you purchase a new house.

Pick a purchase price for a home.

Determine a reasonable current market nominal annual interest rate for your home loan.  (you can check the internet).

Pick either a 15 year or 30 year loan. (180 or 360 monthly payments)

What will your monthly loan payment be.  Show your work.

1. a. Purchase price: $300,000
2. b. Interest rate: 4%
3. c. Loan type: 30 year
5. Explanation:
6. If you finance the purchase of a brand-new home for 30 years at an interest rate of 4% and utilize the funds to pay off the purchase price of $300,000, your monthly loan payment will be $1414.29.
7. In order to get at this result, we must first determine the monthly interest rate, which may be accomplished by dividing the yearly interest rate by 12.
8. 4% / 12 = 0.33%
9. Next, we need to determine the amount of the loan, which may be accomplished by multiplying the cost of the investment by the following loan-to-value ratio:
10. $300,000 * 0.80 (80% loan to value) = $240,000
11. Using the following formula, we are able to determine the amount of the monthly payment now:
12. The formula for determining the monthly payment is as follows: loan amount multiplied by the monthly interest rate divided by the number of payments minus one.
13. $1414.29 = $240,000 * (0.0033 / (1 – (1 + 0.0033)^(-360)))
14. If all 360 payments are made on time, the total amount you will have paid off at the conclusion of the loan will be $506,786.40. This amount includes $266,786.40 in interest. If you make all 360 payments on time, the total amount you will have paid off will be $506,786.40.