Project Requirements

Project #2 – Capital Budgeting

FINA 4630 Financial Modeling

• Use the spreadsheet file Project #2 – Loan Amortization – Shell as the basis for your model.
• Use the spreadsheet file: Project #2 – Capital Budgeting – Shell as the basis for your model. This project should include all of the Visual Basic buttons labeled on the excel sheet. Everything for this project must be created following the worksheet key on the excel sheet. (i.e. crossover rate must be solved using solver etc.)
• Build a worksheet which evaluates and compares two capital budgeting projects using the following capital budgeting decision-making techniques:
  • Net Present Value (NPV) – Use Excel function NPV
  • Internal Rate of Return (IRR) – Use Excel function IRR
  • Modified Internal Rate of Return (MIRR) – Use Excel function MIRR
  • Payback Period – User Defined function (See below for code)
  • Discounted Payback Period – User Defined Function
• The cash flows for the two projects are as follows:
• The Capital Budgeting worksheet should be able to handle projects with up to ten years of estimated cash flows. The cash flows are user input variables. (Note: you do not need to use if statements in this case but all 10 years should be included in your equations.)
• Initially, let cell C6 on the Capital Budgeting worksheet file be a user input variable. The base case value is 11%. In cell E6, use the formula = C6. You will be changing the contents of cell C6 in the VBA portion of the project.
• Construct a Data Table that shows the NPV of each project for discount rates ranging from 1.0% to 30.0% in increments of 0.5%. Place this Data Table in the Range O7:Q65 on the Capital Budgeting worksheet.
• Construct a Chart containing the NPV Profiles for each project. As always, make sure the chart is properly labeled and professional in appearance (also identify all of the important points on the chart). Place this Chart on the worksheet entitled NPV profiles.
• Which project should you invest in based on NPV, IRR, MIRR, Payback Period, and Discounted Payback Period? If you get conflicting answers which of the five calculations should you always use to identify the best project.

Rename your Excel file Last_Name, First_Name Project 2

Payback Period function code (function called PBP).

Function PBP(Investment, Cash_Flows) As Double

‘

‘ This function computes the Pay Back Period (PBP)

‘ The initial investment should be a negative number (outflow).

‘ The function can handle up to 50 periods of cash flows.

‘

Dim CF_Array(1 To 50) As Double

Dim i As Integer, j As Integer, L As Integer, Item As Variant

i = 0

PBP = 0

Investment = Investment * -1

‘

For L = 1 To 10

CF_Array(L) = 0

Next L

‘

For Each Item In Cash_Flows

i = i + 1

CF_Array(i) = Item

Next Item

‘

j = 0

For Each Item In Cash_Flows

j = j + 1

PBP = PBP + Item

If PBP > Investment Then Exit For

Next Item

‘

j = j – 1

PBP = j + ((Investment – (PBP – CF_Array(j + 1))) / CF_Array(j + 1))

‘

End Function

End of Cash Flows

Period Project L Project S

0 ($175,000) ($152,000)

1 $50,000 $65,000

2 $50,000 $50,000

3 $50,000 $40,000

4 $50,000 $30,000

5 $50,000 $20,000

Check Figures (Base Case):

Project L

Net present value (NPV) = $9,794.85

Internal rate of return (IRR) = 13.20%

Modified IRR (MIRR) = 12.22%

Payback period = 3.50

Discounted payback period = 4.67

Crossover rate = 12.36%

Make sure you plug in different numbers to make sure your model is working correctly. Such as including cash flows for all 10 years.

When you are finished close all excel files and open only this project. Verify that all the macros have been saved correctly and are working.

When submitting your project, please make sure that the version of the model you are submitting represents the “BASE CASE” values for all variables. Do not move any of the cells around or show work outside of the designated cells.