Measuring balance over time
1. Measuring balance over time
Up to now, all the periods that we have been working with on our schedule have just been identified by a number. In this section, we'll add dates to our schedule based on the frequency of payment. We will also add a new field to calculate the loan-to-value against the underlying value of the asset being purchased.2. Dates vs. periods
Quick! Think of the date which is 18 bi-weekly periods forward from December 31, 2018. It's not something which is easy to determine quickly. In fact, you'd probably want to have your spreadsheet determine it - so that you can see when certain milestones are hit. Maybe you have money coming in, like a bonus, and are looking at times to make an early paydown. Or, like most people with a mortgage, are looking for the day that you become debt free.3. Loan date and payment dates
In order to add dates to our schedule, we need to add 2 additional fields to our amortization schedule. The first is the date when the loan starts; for now, we'll assume that loans start on the last day of the month since there are some more advanced calculations required for mid-month loans. The second is the date of the payments that are being made, which should be right beside the period field.4. New Function - EOMONTH()
To determine what date to use for our schedule, we will use the EOMONTH() function, which provides the last day of the month for a given start date. In our schedule, if the loan date was December 31, 2018, and we wanted to know 3 months after that date, then we would enter EOMONTH, December 31, 2018, then 3. This would return the last day of the 3rd month after December; that is, March 31, 2019. It doesn't matter if the date is the last day of the month, but we'll be using the last date of the month to avoid some trickier calculations that we'll be discussing later in the course.5. Loan to value
Until now, the example we have been using is a student loan - something which doesn't have tangible value that a loan could be evaluated against. Now, we'll introduce the idea of a car loan, where there now is an asset, the BMW in the picture, that the loan can be valued against. This introduces the idea of loan to value, where the loan balance is divided by the value of the asset. It can't normally be higher than 100%, and will never be below zero. A higher loan to value could impact insurance rates, or increase the underlying interest rate to reflect the higher lending risk.6. Asset value and loan to value
We'll now be adding our asset value and loan to value on to our amortization table. The asset value normally goes close to the principal value on the spreadsheet, as they are the only dollar amounts that are inputted. This value should never be a higher value than the underlying loan. The new loan to value, LTV, goes to the right of the closing balance and is always calculated by taking the closing balance from column N and dividing by the asset value, that is in cell B4. For our purposes in this course, we're going to assume that the asset value never changes; that's a problem for the accountants!7. Time to practice dates and LTV!
That's two new concepts that we've introduced in this lesson - finish up with those, and we'll be ready to finish off our dashboard in the next chapter.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.