Copy of UnderstandingFinancialFunctions

Copy of UnderstandingFinancialFunctions - Using Financial...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Using Financial Functions This business math workshop will help you to understand how to use certain Financial Functions available through the Fun wizard provided by Excel. The function wizard has many available financial functions, but we will only concern ourselves with types in the beginning class. The functions we will review are the Future Value, Present Value, Rate, and NPer, although more available. To begin, please click on the Understanding Finance Functions worksheet, and review how each functio works. Once you are sure of your understanding, click on the On Your Own worksheet and complete the f questions. After completing the questions, save and submit for grading. available through the Function ly concern ourselves with a few and NPer, although more are iew how each function eet and complete the four ading. UFF 1. You are interested in determining how much you will need to have saved up if you expect to retire in 20 years. Presently, your montly requirements for expenses is at 2,500 a month, and you expect to live for 20 years after you retire. You are projecting the cost of living to stay at about 5%. Time to retire: Interest Rate: Monthly Requirement: Future value: 20 5% 2500 $1,027,584.17 What we are trying to find out is how much money do you think you will need to have set aside for the future. Given that the cost of living is at 5% (or so you project), and you want to retire in 20 years, and currently need $2,500 a month to get by. The interest rate is divided by 12, as the interest rate is on a yearly basis, and we need to turn it into a monthly basis. The number of periods is the amount of time we have until we expect to retire. We expect to retire in 240 months. (The interest rate needs to match the nper, (quarterly, monthly, yearly)). The Pmt is the amount that we need each month. Notice it is a negative number. That represents a cash outflow amount. UFF 2. Now that you know how much you will need, we need to look at the payment necessary to have that much saved Time to retire: Interest Rate: Future Value Needed: Payment 20 8% $1,027,584.17 $1,855.75 We need to determine the payment. Starting with the PMT financial function, we will analyze the necessary parts. The interest rate is divided by 12, as the interest rate is on a yearly basis, and we need to turn it into a monthly basis. The number of periods is the amount of time we have until we expect to retire. We expect to retire in 240 months. (The interest rate needs to match the nper, (quarterly, monthly, yearly)). In this case we don't know the present value amount (as it isn't a lump sum available right now), but we do know what we need in the What pieces do i need t financial problem? Regardless of what we are solving for, eithe value, or how long it will take to pay for so questions involves knowing some form of t value (how much did i borrow?), future va future), interest rate, and number of paym payment. Each type of problem will be worked throu and understand the missing component and problem. Then, the On your own workshee We need to determine the payment. Starting with the PMT financial function, we will analyze the necessary parts. The interest rate is divided by 12, as the interest rate is on a yearly basis, and we need to turn it into a monthly basis. The number of periods is the amount of time we have until we expect to retire. We expect to retire in 240 months. (The interest rate needs to match the nper, (quarterly, monthly, yearly)). In this case we don't know the present value amount (as it isn't a lump sum available right now), but we do know what we need in the future. You will only need to use either the present value OR the future value, there is no need to include both UFF 3. Fina We want to buy a house, but can only afford $800 a month (not including the taxes and other fees) How much of a house can we afford if we have an interest rate of 6.5%, 30 year term? Term Interest Rate: Home Price Payment 30 7% $126,568.66 $800.00 We know the payment, what we don't know is how much (Home price, or present value). For this problem, we'd need to use the PV function to determine what the initial loan amount would be to fit our needs UFF 4. We are looking at buying a car, and our credit is less than perfect. We have found one, and the dealer has said they could get us the payment we want, but won't come out and tell us what interest rate we are being charged. Term Interest Rate: Principal Payment 4 12.7% $15,000.00 $400.00 Notice that the rate will return the interest rate per term (in our case assessed per month), so we needed to multiply the rate results by 12 to get the annual rate charged =RATE(E78*12,E81,-E80)*12 Notice that the rate will return the interest rate per term (in our case assessed per month), so we needed to multiply the rate results by 12 to get the annual rate charged =RATE(E78*12,E81,-E80)*12 What pieces do i need to solve a financial problem? Regardless of what we are solving for, either the present value, future value, or how long it will take to pay for something, each of these questions involves knowing some form of the basic components: present value (how much did i borrow?), future value (what is it worth in the future), interest rate, and number of payments i will make and payment. Each type of problem will be worked through, so that you can go through and understand the missing component and understand the solution to the problem. Then, the On your own worksheet is available for you to try. OUO 1 What will the payment be for the cool stereo that you want? The interest rate is at 21%, you want to finance it for 3.5 years, and its cost is 1,500. Interest Rate: Term Principal Payment Total Interest Paid How much of this is going to interest? Multiply the payment amount by the number of payments. Then subtract this number from the original amount financed. The difference represents interest paid for using someone elses money. OUO 2 30 year You have a desire to pay off your home in 15 years rather than 30. How much would the payment need to be if the interst rate was 5.5%, the principal amount was $150,000. More importantly, how much would you save by paying off the house early? Interest Rate: Term Principal Payment 15­Year Interest Rate: Term Principal Payment Total amount saved by paying off early: In order to determine how much you'd save by paying of the mortgage early, you'd need to multiply out the original payment amount by the original term, and then subtract that amount from the 15-year payment for the 15-year term OUO 3 You are thinking of buying a car, but can only afford $175 a month for a payment. How much of a car can you afford (principal) if the current interest rate is 4.5%, and you are financing for 4 years? Interest Rate: Term Principal Payment What is the unknown in this case? We don't know the present value (principal), so you'd need to use the PV function What is the unknown in this case? We don't know the present value (principal), so you'd need to use the PV function OUO 4 You have gone into rent america to rent/buy a television. The payments are going to be $75 a month, for 2 years, on furniture worth about $1,000. What interest rate are they charging you? Interest Rate: Term Principal Payment ...
View Full Document

Ask a homework question - tutors are online