Amortization Schedule 1 Use an Excel function to calculate the monthly payment for a 15 year loan with a principal amount of $250,000, and an annual interest 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 (Youre 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 whats left over after youve 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, using Ctrl Shift Down Arrow will select to the bottom of a list ) Amortization Schedule 1 $ 3 Loan (Principal) 4 Annual Interest 5 Term (years) Pmts per Year Payment Type Ending Balance $ Payment $ , $ $ $ Totals Payment Number , Regular Payment , Toward Interest , Toward Principal $ $ , $ , , , , , , , 16 2 777 27 $ , , , , , , , Balance , , , , , , , , , 7222 2

Question

Amortization Schedule 1 Use an Excel function to calculate the monthly payment for a 15 year loan with a principal amount of $250,000, and an annual interest 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  (Youre 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  whats left over  after youve 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, using Ctrl Shift Down Arrow will select to the bottom of a list ) Amortization Schedule 1 $ 3 Loan (Principal) 4 Annual Interest 5 Term (years)           Pmts per Year Payment Type Ending Balance $        Payment $  ,      $ $ $ Totals Payment Number  ,      Regular Payment  ,      Toward Interest  ,      Toward Principal $ $  ,      $         ,       ,       ,       ,       ,       ,              ,      16 2 777 27 $  ,              ,       ,       ,       ,       ,              ,      Balance  ,       ,       ,       ,       ,       ,       ,       ,       ,             7222 2

Accepted Answer

The Answer is in the image, click to view ...

Question

Amortization Schedule 1 Use an Excel function to calculate the monthly payment for a 15-year loan with a principal amount of $250,000, and an annual

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Statistical Audit Automation Applying Computer Assisted Audit Techniques

Students also viewed these Accounting questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question