Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

For this assignment, you are to turn in an Excel workbook containing three sheets: one worksheet and two chart sheets. Please label the sheets as

For this assignment, you are to turn in an Excel workbook containing three sheets: one worksheet and two chart sheets. Please label the sheets as specified below. Make sure the charts have a good format. In particular, pay attention to labels and titles for the axes. Jane is borrowing a $20,000 and would like to make a plan for paying off this loan. Her annual interest rate is 6% and wants to reduce her debt to $0 within 12 months. She is planning to make a fixed monthly payment and want to know how much her monthly payments are, and how much will her interest and principal payments be each month. She has arranged to make the payments due at the end of the month starting in January 2023. a. Set up a worksheet with all the relevant numbers above. Make sure to label these values correctly. b. On the same worksheet, create a table that shows, for each month: the date of payment, the number of the payment, the total payment, interest payment, principal payment, and ending (remaining) balance for the 12-month period. Rename the worksheet as Payment Table. c. Be sure every number is well-formatted and well-labeled. d. Create a line chart on a separate sheet that shows the ending balance as it changes over time. The labels on the horizontal axis should be dates. Hint: You may do it the following way: Select the Ending Balance column and then insert your chart. Then use Chart Tools/Design/Data/Select Data and edit the horizontal axis to be labeled by the Date column. Be sure the vertical axis starts at 0. Rename the worksheet as Payment Balance Chart. e. Create a stacked column chart on another sheet that shows interest payment and principal payment for each month. Be sure to change the horizontal axis labels to show the dates. Rename the worksheet as Payments Chart. f. In the Payment Table worksheet, show what the net present value of the payments. g. Also, show how much her monthly payment would be if she makes an extra ending payment of $1500. You can think of this as a negative future value, i.e., your monthly payments should be smaller if you are making some extra payment during the last payment period.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Theory Of Interest

Authors: Friedrich A. Lutz

2nd Edition

1138539074,1351472836

More Books

Students also viewed these Finance questions

Question

Discuss the steps in processing business transactions.

Answered: 1 week ago