Instructions

10 POINTS

Module 5 Homework: Project Analysis Assignment

Loan Amortization and Time Value of Money calculations help managers evaluate the best use of the funds for which they have access. It is common for a manger to have many options for where to spend the money within the organization. A manager often needs to make decisions such as delaying an expense in one area to the next budget year to pay for another expense somewhere else in this year.

To complete this assignment, I have included the Module_5_Worksheet in excel for you to download. There are 2 tabs at the bottom for the 2 different question sets.

Using the Loan Amortization Schedule on the excel sheet, respond to the following questions.

Loan 1:

Principal borrowed: $100,000

Total Payments: 30 years (360)

Annual interest rate= 5%

Start date of the loan: 1/1/2020

1. What is the monthly payment (Principal and interest)?

2. What is the total interest paid?

3. Total of 360 Payments?

4. At what payment number does the amount going to the actual principle exceed the amount allocated toward paying interest?

Loan 2:

Principal borrowed: $250,000

Total payments: 15 years (180 months)

Interest rate= 7%

Start date of the loan: 1/1/2020

1. What is the monthly payment (Principal and interest)?

2. When making payment number 24, how much of your payment is going toward interest?

3. After paying on the loan for 5 years, you have made 60 payments. When you make your 61st payment, is at least half of your payment going toward paying down the principal of the loan?

Now, using the NPV and IRR Calculator tab on the excel sheet, enter the following information, and respond to the following questions:

This type of document can be used to evaluate capital projects that are going to be depreciated over a specific period of time, in this case, 7 years. Your initial investment is always a negative number, because it is money that is going out to purchase the required items for the project.

The IRR (internal rate of return) is then compared against the cost of capital. The cost of capital can be looked at as your benchmark to approve this project. The number represents the amount of money you truly make once you discount the future cash flows. For our purposes here, if the IRR of your project is less than what you could make with investments, you should reject the project.

Investment: $500,000 (remember, this is cash spent, so it needs to be entered as a negative number)

Cash Flows: $100,000 each year for 7 years

Cost of Capital: 11%

1. What is the NPV of this project? Would you approve it?

2. What is the IRR?

Now, change the Cost of Capital to 8%

3. Explain in no more than 150 words, why the IRR stays the same, but the NPV is now positive?

Loan Amortization Schedule Enter values Loan amount Annual interest rate Loan period in years Number of payments per year Start date of loan Optional extra payments Loan summary Scheduled payment Scheduled number of payments Actual number of payments Total early payments Total interest Lender name: Pmt No. Payment Date Beginning Balance Scheduled Payment Extra Payment Total Payment Principal Ending Balance Cumulative Interest Interest Loan Amortization Schedule NPV and IRR Calculator + C F G H I B Amount $0 $0 D NPV Calculation IRR Calculation Cost of Capital Approve Project E $0.00 #NUM! 0.00% Yes SO No 1 Payments 2 Initial Investment/Payment (YR 0) 3 YR 1 Cash Flow 4 YR 2 Cash Flow 5 YR 3 Cash Flow 6 YR 4 Cash Flow 7 YR 5 Cash Flow 8 YR 6 Cash Flow 9 YR 7 Cash Flow 10 42 43 44 Loan Amortization Schedule NPV and IRR Calculator Loan Amortization Schedule Enter values Loan amount Annual interest rate Loan period in years Number of payments per year Start date of loan Optional extra payments Loan summary Scheduled payment Scheduled number of payments Actual number of payments Total early payments Total interest Lender name: Pmt No. Payment Date Beginning Balance Scheduled Payment Extra Payment Total Payment Principal Ending Balance Cumulative Interest Interest Loan Amortization Schedule NPV and IRR Calculator + C F G H I B Amount $0 $0 D NPV Calculation IRR Calculation Cost of Capital Approve Project E $0.00 #NUM! 0.00% Yes SO No 1 Payments 2 Initial Investment/Payment (YR 0) 3 YR 1 Cash Flow 4 YR 2 Cash Flow 5 YR 3 Cash Flow 6 YR 4 Cash Flow 7 YR 5 Cash Flow 8 YR 6 Cash Flow 9 YR 7 Cash Flow 10 42 43 44 Loan Amortization Schedule NPV and IRR Calculator