Question
Create a new excel file for this assignment Part 1 1)In the excel file, the first worksheet of the file should be named Part 1.
Create a new excel file for this assignment
Part 1
1)In the excel file, the first worksheet of the file should be named Part 1. 2
)In cell A1 write out the formula to calculate present value.
3)In this exercise you will calculate the Present Value based on the following parameters:a.X = $14,000 b.N = 5 yearsc.r = 7.5%
4)Using the same parameters as #3 above also show the proof that the present value equals future value.
Part 2
1)Create a new worksheet in the file and name it Part 2.
2)Using the same parameters as in #3 above create the following: a.Create a table with a column of discount rates from 2% to 25% in increments of a half percentage. For example, 2%, 2.5% 3% and so on.b.In the table create a column to calculate the present value at each discount rate. c.Create a line chart showing the present value for each discount rate.
Part 3: (See next page for the variables in the edit screen of the Present Value function)
1)Create a new worksheet in the file and name it Part 3.
2)Think about the exercises we did in class with the investments, loans and lottery. For the following exercises create a box for each exercise as we did in class and put all the information in the box. Use the present value function to calculate the present value. You are always calculating the PV based on receiving the funds at the end of the period (where it says type)
3)Here is the information for the following exercise regarding taking out a loan:a.You take out a personal loan for 10 years.b.The interest rate = 8%c.You can afford to make monthly payments of $760 for 10 yearsd.Based on the present value, how large of a loan can you afford to borrow?
4)You just won $100 million in the lottery. The Lottery can either pay you a lump sum right now or pay you once a year for 25 years. a.Interest rate = 3%b.Lottery pays you $4 million once a year for 25 yearsc.What is the present value of the lump sum? 2 5)You are saving for retirement. You want your retirement fund to reach $5 million in 40 years. Based on the below parameters, what is the present value of what youll need to invest today?a.Interest rate is 5.5%b.You are making monthly payments of $800 for the entire period
Inputs for present value are:Rate = the annual interest rate. If there are monthly payments, the rate should be divided by 12. For example 5%/12.NPER = Total number of payment periods. NPER is multiplied by the number of years for example 12 payments a year for 5years = 12*5.PMT = Payment made each period. If it is more than an initial payment, then PMT should be the amount you pay each period. For example $500 is paid each month.FV = Future value depends on if you are receiving funds or paying out funds. For example in an investment, the future value is how much you would receive in the future. For a loan payment schedule it would be $0 because you paying down a loan.Type = Use 0 or leave empty to represent payment made at the end of a period. If you enter 1, it represents a payment made at the beginning of a period. For this class we will leave it empty.
Part 4 (The worksheet should be called Part 4)
1)Effective RateSetup the effective rate format the same as in the video and calculate the effective rate to include:Annual Semi-annual Quarterly Bimonthly Monthly Weekly Daily
b) Use a nominal rate of 9%. Automate the nominal rate by placing it in cell A1c) Use the effect function to calculate the effective rate d) Create a column to calculate the effective with an equation to confirm the effective rate
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started