# financial modeling 2

Finish all 3 tasks FOLLOWING THE INSTRUCTIONS GIVEN IN THE WORKBOOK.

Task 1: Prepare an XY chart (aka scatter chart) of either Inflation or GDP growth against Year.

The users should be able to define the start and end dates of the chart. (The chart should show at least 5 years of data.)

a. Use dynamically named ranges in data validation for “StartYear” and for “EndYear”.

b. Use dynamically named ranges to define “XAxis” and “YAxis”

Create an amortization table (I have already named it TableAmort) that is populated automatically as the user enters the conditions of a loan. As a starting value, use the following information: A \$200,000 30-year mortgage with 10% annual mortgage rate. Payments are paid at the beginning of each month.

a. Must use named ranges (names in column C)

b. No hard coding in TableAmort and no additional column may be created for TableAmort. For Month, you may consider using the function =ROW().

c. Must use IPMT and PPMT. You should use CUMPRINC in the column for Ending Balance.

d. The formulas in every row should be the same as in the first row.

e. Must be applicable to all mortgages up to 40 years.

f. Must not display any kind of error in the table.

Create a capital budgeting calculator where the following five measures are automatically calculated given the user-defined cash flows and interest rates.

NPV, IRR, MIRR, PP, and PI

PI is profitability index. PI =

SUM(PV of Positive CF)

SUM(PV of Negative CF)

To get started, you may use the following cash flows (from year 0 to year 5):

-120,000; 39,000; 30,000; -21,000; 37,000; 46,000

r= 10%; finance_rate=10%; reinvest_rate=12%

The column “CashFlow” is the only column that can be hard-coded.

(hint: For the calclation of the payback period, we have introduced a method using MATCH and INDEX