very basic use of PV and PMT function on excel to fill in 2 tables. Should not take more then 5 minutes for people that are used to excel. See the files attached. thankyou

MIS 2000 – WINTER 2020 – Version 1

Excel Lab 3:

Frequently Used Financial Functions

Concepts:

PV, PMT, RATE, NPER
Amortization Table

Instruction

This lab is individual

For this lab, please download a workbook named “MIS2000-Excel-Lab3-posted”, and save it under the new name:

“MIS2000-Excel-Lab3-LastName-FirstName”, replace LastName-FirstName with your real name.

-------------------------------------------------------------------------------------------------------------

For Part A, you must follow all of the YouTube videos listed below. One by one, complete the exercises as instructed by the video. You will see all the sample data has been entered for you in the “MIS2000-Excel-Lab3” workbook.

For Part B, you MUST:

Use the specified function/formula to solve the problem. Otherwise, you will receive zero for the answer.
Use cell reference (relative and/or absolute cell references) properly. You will lose marks for not using cell references.
Format the cells so that the numbers that represent money (dollars) will have “$” in front of them, the number should also have the American version of displaying cash figures for example: $1,000.00.
- This is the 1000 separator and two decimal places.

Upload your completed file to the UMLearn digital dropbox named: Lab-Excel3. Make sure you submit the correct file.

Part A. YouTube Exercises

1. YouTube video (about 8 minutes, the video also has an amortization table)

Title: Excel Finance Functions: PMT() • PV() • RATE() • NPER()

https://www.youtube.com/watch?v=AfWRp1mExQw

There are four worksheets for you to practice in the workbook.

3-1 PMT
3-1 PV
3-1 RATE
3-1 NPER & Amt. Table

From Corporate Finance, you would know these acronyms.

PMT = Payment; PV = Present Value; Rate = Interest Rate; NPER = Number of periods; Amt. Table = Amortization Table.

YouTube videos per section:

PMT = 0:00 – 4:15
PV = 4:45 – 5:35
RATE = 5:35 – 6:28
NPER = 6:28 – 6:59
Amt. Table = 6:59 – end

Part B. Instructor-Designed Homework

TA Notes:

Pay attention to Part A and internalize the concepts.
Syntax of Functions is important.
Ensure that your inputs are ALL correct.
This part of the lab really requires for you to understand the functions you have learned earlier as well as logically think on how it all fits together.
You’re not always given everything to you, you sometimes must find things yourself.

Lab3-HW1-PV: Estimating Your Ability to Buy a House (PV Function)

You have graduated from Asper two years from now, and you have learned a lot of useful knowledge and skills from the MIS classes, which helped you get a job with a salary of about $45,000 a year. After happily having worked for two years, you decide to purchase a house. You try to estimate the price of the house that you could afford.

You calculated that you can put $1,000 as the monthly payment for the mortgage. The mortgage annual interest rate is 2.35%, and you plan to pay it off in 30 years.

You must use the PV function to do the calculation.

An Assumption in the Scenario:

Assume that you have saved $30,000 for down payment, so the house that you could afford is “Affordable house price” + your down payment. But in your calculation, we did not include the down payment.

(hint: the correct answer is illustrated below). very basic use of PV and PMT function on excel to fill in 2 tables. Should not take more then 5 minutes for people that are used to excel. See the files attached. thankyou 1

Lab3-HW2-PMT: Paying a House Mortgage (PMT function)

From Lab3-HW1-PV exercise, you are disappointed to find out that the affordable house for you would be very, very old and small. You have decided to keep on renting an apartment before you buy a house. You have waited for some more years, saved more money for down payment, and can make more monthly payment.

You have already found your dream house and need to borrow $280,000 from a bank. The interest rate is 3.25% Unfortunately, the mortgage interest rate increased .

You plan to pay off the loan in 30 years. You want to calculate the bi-weekly and monthly payment for the house mortgage.

Assume 52 weeks per year for the bi-weekly payment. Bi-weekly here means every 2 weeks.

An Assumption in the Scenario:

Usually, mortgage contract is for five years, and then the borrower needs to sign another loan/mortgage contract, the interest rate will likely to change, however, for this case, just assume the interest rate does not change.

No correct solution is provided, you will need to figure out by yourself. 

---------------------------------------------------------------------------------------------------------------------

Lab3-HW3-Rate: Determining the Interest Rate of a Diamond

(Rate function, and Amortization Table)

You have moved in the dream house with your spouse. You decide to buy yourself or your spouse a diamond ring. Your budget is $10,000.

Before Christmas, you have received some ads for diamond rings. There is a ring with the listed price of $10,000, and the ads also lists the option for the monthly payments (only $298.99/month) for three years.

You think the monthly payment option looks good, but… you want to find out how much the vendor charges you for the interest rate, and how much interest that you must pay each month.

REQUIRED:

Fill out table 1 (Calculate the year rate) to get the annual rate
Fill out table 2 (Amortization table) to get how much interest would be paid each month.
Format numbers (money and interest rate) in both tables with two decimal places.

No correct solution is provided, you will need to figure out by yourself. 

----End of the assignment----

5/5