30 Jun Question Description Instructions Complete Problem 1 through 6 in t
Question Description
Instructions
Complete Problem 1 through 6 in the provided Excel template. Use Excel or save as an Excel file (.XLS or .XLSX) for all calculations. All formulas should flow through the spreadsheet and entries should not be hard-keyed. Use APA formatting to cite any resources used. To access this assignment: 1-3 Graded Valuation Problem Set
Note: When submitting Problem Set assignments, regardless of what software you are using to perform the assignment, please export or save your assignment as an Excel .XLS or .XLSX file to receive instructor feedback.
Tags: finance Graded Valuation Problem Set
fin_691_1_3_graded_valuation_problem_set.xlsx
Unformatted Attachment Preview
INSTRUCTIONS: calculate Payment and fill-in the table You are thinking about buying a house for $234,000. You are thinking about aggressively paying off the mortgage in 10 years. T funded loan and payments at the end of the year are flat. You are still required to pay back the interest on this loan at 6%. 1) Calculate the appropriate Loan Table 2) Show the breakdown in each year between principal and interest Cost Payment Interest <- from problem <- formula to calculate PMT <- from problem Division of payment between: Principal at beginning of year Year 1 2 3 4 5 6 7 8 9 10 11 0 Payment at end of year Interest Principal he mortgage in 10 years. This is a family t on this loan at 6%. Instructions: Use Excel Solver to calculate Initial Payment You are offered an investment with the following conditions: - The cost of investment is $2,000 - The investment pays out a sum X at the end of the first year; this payout grows at the rate of 5% per year for 10 years. If your discount rate is 10%, calculate the smallest X which would entice you to purchase the asset. For example, as you can se USE: Excel Solver Discount rate Initial Investment Initial payment Growth Rate NPV Year 0 1 2 3 4 5 6 7 8 9 10 GIVEN GIVEN Used Excel Solver GIVEN Cash flow -2000.00 0.00 <-Initial PMT 0.00 <- PM year before *discount rate 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Instructions: Use Goal Seek and calculate IRR and fill-in the Division of Payment Table In this exercise we solve iteratively for the internal rate of return. Consider an investment which costs 1000 and has cash flows o years 1–5. Setting up the loan table below shows that 10% is greater than the IRR (since the return of principal at the end of yea the beginning of the year): USE: Goal Seek IRR? Year Cash flow LOAN TABLE Principal Year at beginning of year 0.00 Division of payment Payment at end of year Interest Principal <-- Should be zero for IRR h costs 1000 and has cash flows of 400, 300, 250, 150, 125 in return of principal at the end of year 5 is less than the principal at Instructions: Calculate monthly payment, fill in the Split of Pymanet table and complete Part c of the question. You have just taken a car loan of $35,000. The loan is for 60 months at an annual interest rate of 18% (which the bank translate of each of the next 60 months) are all equal. a. Calculate the monthly payment on the loan. b. In a loan table calculate, for each month: the principal remaining on the loan at the beginning of the month and the split of tha c. Show that the principal at the beginning of each month is the present value of the remaining loan payments at the loan interes Loan principal Interest rate annual monthly Loan term (months) Monthly payment Split of payment Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 Principal at beginning of month Payment Interest Principal 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 c of the question. % (which the bank translates to a monthly rate of 18%/12 = 1.50%). The 60 payments (to be made at the end e month and the split of that month’s payment between interest and repayment of principal. payments at the loan interest rate (use either NPV or the PV functions). Part c of question PV of remaining payments Same answer using PV Instructions: use XIRR to calculate IRR Use function XIRR to complete the following: Date Cash flow 20-May-10 -1599 14-Jan-11 85 14-Jan-12 85 14-Jan-13 85 14-Jan-14 85 14-Jan-15 85 14-Jan-16 2,599 IRR Instructions: use XNPV to calculate Net Present Value Use XNPV to value the following investment. Assume that the annual discount rate is 20%. Discount rate Net present value Date 20-May-10 14-Jan-11 14-Jan-12 14-Jan-13 14-Jan-14 14-Jan-15 14-Jan-16 20.00% <-use XNPV formula Cash flow -500 80 200 300 80 80 80 Instructions: using the NPV calculated in problem 6-step 1, and calculate NPV using SOLVER, highlighted in yellow. Use XNPV to value the following investment. Assume that the annual discount rate is 20%.(Use data from Problem 6 Step-1) Discount rate Net present value Date 20-May-10 14-Jan-11 14-Jan-12 14-Jan-13 14-Jan-14 14-Jan-15 14-Jan-16 0 <-NPV from Problem Step 1 Cash flow -500 80 200 300 80 80 80 Identify the two internal rates of return of the investment from the data above. Solver limits to identify both IRRs: Lower 15% Upper 45% Using Solver screen calculate NPV below: NPV? ...
Purchase answer to see full attachment
Studypool values your privacy. Only questions posted as Public are visible on our website.
Tutor Answer
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.