Go to the Tex & Edna - Loan worksheet to complete steps 11 through 19. 11....
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Go to the Tex & Edna - Loan worksheet to complete steps 11 through 19. 11. Enter a formula in cell B8 that calculates the periodic interest rate (the interest rate per payment Excel Expert: Assignment 1 for Excel 365 period). Do not use a function in your formula. As always, make sure to use references rather than typing numbers into your formula, otherwise marks will be deducted. For example, if you need the loan amount in cell B4, enter a reference to B4 rather than typing 2000000 into your formula. 12. Enter a formula in cell B9 that calculates the number of payments in the loan. Do not use a function in your formula. 2 13. Enter a formula in cell B13 that uses the PPMT function to calculate the principal repaid on the first loan payment. Your formula must return a positive value. If you need to use the values calculated in steps 11 or 12, make sure to reference them rather than recalculating these values in your formula, otherwise marks will be deducted. This also applies to the remaining questions in this worksheet. 14. Enter a formula in cell B14 that uses the IPMT function to calculate the interest paid on the first loan payment. Your formula must return a positive value. 15. Enter a formula in cell B15 that uses the SUM function to calculate the total of the first loan payment. 16. Enter a formula in cell F5 that uses the CUMPRINC function to calculate the cumulative principal repaid after the first year. You will need to reference cell E5 in your formula for the end payment number. Your formula must return a positive value. Copy cell F5 to cells F6 through F14. 17. Enter a formula in cell B18 that uses the NPER function to calculate the number of periods in the loan. You can use the total first loan payment calculated in cell B15 for the payment amount since all the loan payments are equal. Make sure to use a negative value for the loan amount in your formula. Your result should match the value you calculated in cell B9. 18. Enter a formula in cell B19 that uses the RATE function to calculate the annual interest rate of the loan. Make sure to use a negative value for the loan amount in your formula. Don't forget to multiply the result returned by the RATE function by the number of payments in a year to obtain the annual interest rate. Your result should match the value in cell B5. 19. Enter a formula in cell B20 that uses the FV function to calculate the future value of the loan amount. Use a negative value for the payment amount, and a positive value for the loan amount. Since all the payments are equal, you can use the payment amount in cell B15. Tex & Edna's Business Loan Loan Details Loan Amount: $ 2,000,000.00 Annual Interest Rate: Payments per Year: Loan Duration (Years): Interest rate per period: Number of payments: First Payment Amounts Principal repaid on first payment: Interest paid on first payment: Total of first payment: Formula Checks: Number of periods in the loan: Annual Interest Rate: Loan Future Value: $1,650,000.00 9.90% 6 10 1.650% 60 $185,595.35 $33,000.00 $218,595.35 9.823894857 #NUM! Year 1 2 3 4 5 6 7 8 9 10 Principal Repaid Payment # Cumulative Principal 6- 123,593.59 12 18- 24 30 36 421- 48 541- 60 #NAME? 370,494.62 516,003.04 678,294.41 859,129.52 1,060,451.31 1,284,403.65 1,533,352.14 1,809,906.91 Go to the Tex & Edna - Loan worksheet to complete steps 11 through 19. 11. Enter a formula in cell B8 that calculates the periodic interest rate (the interest rate per payment Excel Expert: Assignment 1 for Excel 365 period). Do not use a function in your formula. As always, make sure to use references rather than typing numbers into your formula, otherwise marks will be deducted. For example, if you need the loan amount in cell B4, enter a reference to B4 rather than typing 2000000 into your formula. 12. Enter a formula in cell B9 that calculates the number of payments in the loan. Do not use a function in your formula. 2 13. Enter a formula in cell B13 that uses the PPMT function to calculate the principal repaid on the first loan payment. Your formula must return a positive value. If you need to use the values calculated in steps 11 or 12, make sure to reference them rather than recalculating these values in your formula, otherwise marks will be deducted. This also applies to the remaining questions in this worksheet. 14. Enter a formula in cell B14 that uses the IPMT function to calculate the interest paid on the first loan payment. Your formula must return a positive value. 15. Enter a formula in cell B15 that uses the SUM function to calculate the total of the first loan payment. 16. Enter a formula in cell F5 that uses the CUMPRINC function to calculate the cumulative principal repaid after the first year. You will need to reference cell E5 in your formula for the end payment number. Your formula must return a positive value. Copy cell F5 to cells F6 through F14. 17. Enter a formula in cell B18 that uses the NPER function to calculate the number of periods in the loan. You can use the total first loan payment calculated in cell B15 for the payment amount since all the loan payments are equal. Make sure to use a negative value for the loan amount in your formula. Your result should match the value you calculated in cell B9. 18. Enter a formula in cell B19 that uses the RATE function to calculate the annual interest rate of the loan. Make sure to use a negative value for the loan amount in your formula. Don't forget to multiply the result returned by the RATE function by the number of payments in a year to obtain the annual interest rate. Your result should match the value in cell B5. 19. Enter a formula in cell B20 that uses the FV function to calculate the future value of the loan amount. Use a negative value for the payment amount, and a positive value for the loan amount. Since all the payments are equal, you can use the payment amount in cell B15. Tex & Edna's Business Loan Loan Details Loan Amount: $ 2,000,000.00 Annual Interest Rate: Payments per Year: Loan Duration (Years): Interest rate per period: Number of payments: First Payment Amounts Principal repaid on first payment: Interest paid on first payment: Total of first payment: Formula Checks: Number of periods in the loan: Annual Interest Rate: Loan Future Value: $1,650,000.00 9.90% 6 10 1.650% 60 $185,595.35 $33,000.00 $218,595.35 9.823894857 #NUM! Year 1 2 3 4 5 6 7 8 9 10 Principal Repaid Payment # Cumulative Principal 6- 123,593.59 12 18- 24 30 36 421- 48 541- 60 #NAME? 370,494.62 516,003.04 678,294.41 859,129.52 1,060,451.31 1,284,403.65 1,533,352.14 1,809,906.91
Expert Answer:
Answer rating: 100% (QA)
It seems you are working on an Excel exercise related to calculating different aspects of a loan Based on the screenshots provided you need to perform various calculations using specific Excel functio... View the full answer
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these programming questions
-
Is an oil pipeline a simplex system, a half-duplex system, a full-duplex system, or none of the above? What about a river or a walkie-talkie-style communication?
-
Please complete a partial worksheet up to the adjusted trial balance for CJ's Mountain Safety Training of Banff, using the following adjustment data and trial balance: a. Supplies on hand, $1,605 b....
-
Let F = f + g and G = 3f - g, where the graphs of f and g are shown in the figure. Find the following derivatives. G'(2) y, y = f(x) 3 y = g(x) %3D 1+ 5 3.
-
Amanda Forbes was hired as a nail technician by Showmann, Inc., in 2011. In 2017, Forbes attended a work-related holiday party where Showmann distributed raffle tickets to employees. One of the...
-
In May 20X5, the newly appointed controller of Butch Baking Corporation conducted a thorough review of past accounting, particularly of transactions that exceeded the companys normal level of...
-
Problem 4. For the 2-point BVP: -u" (x) + 4u - sin(u)=0 on 0 x 1 with u(0)=u(1)=0. i) (1 point) Write down the main procedures of the shooting method for the BVP with the unknown parameter u'(0)=a,...
-
Suppose you are a bank lending officer at the Midtown National Bank considering a loan request from Miller Manufacturing company for $1.05 million. The firm currently has $1 million in equity and its...
-
You discovered the following errors in connection with your examination of the financial statement of Pasadea Corporation: a. Sale of merchandise on account on December 31, 2022 amounting to P250,000...
-
1. Discuss: Is it okay to get angry at work? What about crying? Would this be the same for someone in a service job where cheerfulness is expected by a customer (e.g. a waiter/waitress)? What about...
-
On 31 May 2023 a motor vehicle had a cost of $55,000 and accumulated depreciation of $38,500. If the motor vehicle was sold for a loss of $2,500 on 1 June 2023, how much was received as the cash...
-
What type of analysis tries to identify at what point tangible benefits equal tangible costs (if ever)?
-
You have just been offered a contract worth $1.16 million per year for 6 years. However, to take the contract, you will need to purchase some new equipment. Your discount rate for this project is...
-
How do you calculate your fixed costs and variable costs when determining your target profit margin?
-
Discuss the function of the central bank. How does it work for the economic development of developing countries?
-
1. True or False. Pitfalls to consider in a statistical test include nonrandom samples, small sample size, and lack of causal links. 2. Because 25 percent of the students in my morning statistics...
-
Indicate, in each of the following situations, the number of exemptions the taxpayers are entitled to claim on their 2012 income tax returns. Number of Exemptions a. Donna, a 20-year-old single...
-
Larry Gaines, age 42, sells his personal residence on November 12, 2012, for $144,000. He lived in the house for 7 years. The expenses of the sale are $10,500, and he has made capital improvements of...
-
If Charles, a 16-year-old child model, earns $50,000 a year and is completely self supporting even though he lives with his parents, can his parents claim him as a dependent? Why or why not?...
-
Write a program in \(\mathrm{R}\) that generates a sample \(X_{1}, \ldots, X_{n}\) from a specified distribution \(F\), computes the empirical distribution function of \(X_{1}, \ldots, X_{n}\),...
-
Let \(\left\{X_{n}ight\}_{n=1}^{\infty}\) be a sequence of random variables such that \(X_{n}\) has a UNI\(\operatorname{FORM}\left\{0, n^{-1}, 2 n^{-2}, \ldots, 1ight\}\) distribution for all \(n...
-
Write a program in \(\mathrm{R}\) that generates a sample from a population with distribution function \[F(x)= \begin{cases}0 & x
Study smarter with the SolutionInn App