Finance 380 – Investment Valuation and Analysis

Finance 380 – Investment Valuation and Analysis

Spreadsheet Assignment 1

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

In this assignment, you will use VLOOKUP and pivot tables to evaluate the recent success of active bond mutual fund managers, use various Text functions to manipulate cell contents, and develop a model that accommodates prepayment on an amortizing loan.

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:

Benchmark-adjusted return = Individual mutual fund return – Return for index.

1. 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).

 

Looking for a similar assignment? Our writers will offer you original work free from plagiarism. We follow the assignment instructions to the letter and always deliver on time. Be assured of a quality paper that will raise your grade. Order now and Get a 15% Discount! Use Coupon Code "Newclient"