Summary
Keywords
Full Transcript
In this lesson you'll use the Excel PMT function to create an elegant mortgage calculator. The calculator has Option Buttons (Radio Buttons) and Combo Box controls to set the Term, Interest Rate, Deposit, Arrangement Fee, Income and Amount Financed. Monthly Repayment and Affordability values are then returned. As with all video lessons from The Smart Method®, there's a sample file download link at the beginning of the video to enable you to work along with the lesson. You may, however, get more from this lesson if you first work through lessons 09-01, 09-02 and 09-03. These are the lessons where best-practice techniques were used to add the controls you see to the sample file workbook and set the relevant cell links. ⌚ Timestamps 0:00 Sample file download instructions, download link, and discussion of sample file. 0:10 Set AutoHide Ribbon and re-scale the worksheet so that everything is visible on screen. 1:04 Calculate the Amount Financed result cell as Property Price - Deposit + Arrangement Fee. 1:35 Calculate the Monthly Repayment result cell using the PMT function. 3:00 Calculate the Total Repaid result cell as Monthly Repayment * Term in Years * 12. 3:20 Calculate the Total Interest result cell as Total Amount Repaid - Amount Financed. 3:40 Calculate the Affordability result cell as (Monthly Payment*12)/Annual Income. 4:15 Connect two of the result cells to the user interface. 4:50 Test the worksheet to confirm that all values are being calculated correctly. 6:25 Link to next lesson, course session playlist and to download sample file set.
Continue this lesson in the app
Install CourseHive on Android or iOS to keep learning while you move.
