Use an Excel function to calculate the monthly payment for a 15-year loan with a principal amount of $250,000, and an annualinterest rate of 5.0%. (Assume that loan payments will start one period from the loan start date. Unless specified otherwise, this is the typical payment arrangement for the majority of loans.)
Ensure that users of the spreadsheet can only enter valid input for the Payment Type.After selecting the cell containing the Payment Type, an Input Message should appear, describing the difference between the options which can be entered as the Payment type, and allowing users select valid “Payment Type” options from a drop-down list.If the user attempt to enter an invalid Payment Type, an Error Alert should appear, explaining the options for entering valid Payment Types.
This spreadsheet should be able to be used by either a bank or a borrower. Therefore when calculating the payment, please use a "neutral" perspective so that your inputs and results are both positive. (Your spreadsheet will not show cash inflows as positive and cash outflows as negative.)
Note: When labeling (or thinking about) the timing of the loan payments, please do not incorrectly think "start of the month versus end of the month". The "date" of the regular monthly payment (1st day of the month, last day of the month, 15th day of the month...) is NOT controlled by the Payment Type. Instead, the Payment Type indicates whether the regular monthly payments start "immediately" at the time of loan closing (at the beginning of the first payment period) or "one month/period after" the loan closing date (at the end of the first payment period).
After calculating the monthly payment as described above, create an Amortization Schedule to show a running monthly balance over the life of the loan, along with the interest/principal breakdown for the payment made each month. (You’re paying the same payment amount every single month, but the proportion of your payment that goes towards interest, and towards principal changes every single month.) The columns of your table should match those shown in the screenshot.
Do NOT use IPMT & PPMT to construct your amortization schedule. Use plain old multiplication and division to calculate the how much Interest you owe for the month (Toward Interest), based upon the current balance from the row above (Previous_month_balance*Monthly_interest_rate). The amount of your monthly payment that gets applied Toward Principal is "what’s left over" after you’ve paid the Interest owed. (Monthly_Payment– Towards Interest).(For each payment (month), the Toward Interest and Toward Principal columns should always add up to equal the Monthly Payment amount.) The balance is the previous balance minus the amount paid Towards Principal. (Any money paid Toward Interest does NOT decrease the balance of your loan!)
Make sure your payments run consecutively for the entire 15 years (through 180 payments). Your Balance after payment 180 should be $0.00. Ensure that you can always see the column headings, even when scrolling down to the payments at the very bottom.Also include Totals at the top of (above) the column headings for Payment Amount, Toward Interest and Toward Principal so you can see how much you will be paying towards each over the life of the loan. (If you did it correctly, the Toward Principal total should equal the original Loan Amount.)
Rather than using the mouse to scroll down to payment 180, and up to payment 1, quickly navigate around the sheet using the keyboard shortcuts:Ctrl+Home,Ctrl+End,Ctrl+Up_Arrow,Ctrl+Down_Arrow. Using these in combination with the Shift key selects to the end of a range. (For instance, usingCtrl+Shift+Down_Arrowwill select to the bottom of a list.)