Description

Finance 380 – Investment Valuation and Analysis

Spreadsheet Assignment 1

Due date: Friday, March 6, 2020                                                                         Prof. D. M. Smith

GENERAL INSTRUCTIONS:

Answer each of the following questions in a file, sheets, and cells exactly as instructed. Unless the question states otherwise, wherever possible use Excel functions. It is important to leave the function in the solution cell (i.e., don’t change “=NPV...” to a number).

Each student must start with a brand new, blank file. Name the file lastname.firstname.Fin380 #1.xlsx. This file will contain all your work for this assignment. Submit a hard (printed) copy of your work and email the file to ds693@yahoo.com. This assignment must be completed as stated in the course syllabus (no collaboration with other students on any step of this project).

QUESTIONS:

1.   In Table 3 below, see current data obtained from Morningstar Direct on 658 U.S. actively managed fixed-income mutual funds. Each fund in Table 3 is trying to outperform one of five benchmark indexes listed in Table 1.

      In your blank Excel file, paste Table 1 into a sheet that you name Indexes. Paste Table 3 into an adjacent sheet that you name Active Fund Performance.

      Using Excel’s vlookup function, in your Active Fund Performance sheet, calculate the benchmark-adjusted return for each of the mutual funds for 1, 3, and 5 years. For each fund, benchmark-adjusted return is calculated as:

a)      Do this in a new sheet called Pivot Tables: Create a pivot table showing the proportion of funds associated with each benchmark that beat the index in the past 1 year. Then do the same for 3 and 5-year returns. You should have three pivot tables (one for each return period), and they should appear in the form of the following (the example below is for a different type of funds).

b)   In a text box in the Pivot Tables sheet, describe the results of the analysis reflected in the tables. Did active management work? Address the following:

i)    Identify which types of managers underperformed and which outperformed.

ii)   Describe whether your conclusions differ based on time period.

iii)  State what your results indicate about the validity of active management.

2.   Copy Tables 4, 5, and 6 into a sheet that you name Text Functions. Do the following entirely using Excel functions, and leave the functions intact.

      a)   First, in a new column to the right of the data in Table 4, use the TRIM function to remove the spaces from the left and right of each cell.

      b)   Next, using whichever of the “Text Functions” you need from the list in Exhibit 15.3 below, split the Table 4 data so that tickers all appear in one column and the company names all appear in the adjacent column. Leave the Excel function names in all the cells.

      c)   Next, using whichever text functions you need, combine the two columns of data from Table 5 into a single column. The format of each cell in the column should be first name, then a space, then last name.

      d)   Using Excel’s manipulation functions, switch the order of the first and last names for each person in Table 6, and insert a middle initial “X.” for each. (e.g., David X. Smith)

Leave the Excel function names in all the cells.

Extracted from “Best Practices for Equity Research Analysts,” by James J. Valentine, McGraw-Hill, 2011. (A great book!!)

3.      Answer this question in a sheet that you name Mortgage.

a.   Create an amortization table for a 30-year mortgage loan that has monthly payments. The values in the input range should be loan face value ($), APR (%), and monthly prepayment amount ($). Make sure the spreadsheet is fully interactive.

b.   Add a column to the right of the table developed in part (a), to keep track of the month that the loan is paid off after taking prepayments into account. Also, just beneath the input range, create a cell that automatically updates the payoff time (e.g., “Payoff time = 14.75 years”).