This videocast will show you how to create a motivation schedule based on the mortgage payment calculator that we created in the previous video cast so the first thing we’re going to do is to figure out what is the beginning balance uh for our amortization schedule it’s going to be placed in i 5. so obviously beginning balance a balance is the same as the loan

Amount so it’s going to be equal to d8 that’s i’m going just to type it d8 press enter and that transfers the loan amount into the beginning balance that we will begin paying at the beginning of our mortgage now the ending balance is a bit trickier first of all we need to figure out uh what formulas we’re going to use here and i suggest to use the if statement

And then present value statement as one of the arguments so in this case i’m going to show you how to use an embedded formula where you embed one formula within another one so i’m just going to type in equals if and now i need to to see what my logical test is going to be and i’m just going to compare the uh number of the year number in which i’m currently is

So this is going to be year one then we’ll move to year two three and so on so right now it’s here one and i’m going to compare this value with the term so once i reached year 15 then i do not have to pay anything else correct it’s only 15 year term so the first thing i’m going to do is to type h5 which is our year we’re in i’m comparing it whether it’s less

Or equal to our term to f to f uh five so i’m just going to type in less or equal to f5 and so this formula when i copy it to the rest for the rest of the years is just going to keep going and comparing it okay here one it’s definitely less than 15 year two definitely less than 15. yeah 15. okay it’s still equal to here 15 but when you move forward then this

Condition becomes false and so this point i will end my uh calculation so i should not have any ending balance once the value of h5 is greater than f5 now the only thing here is that you remember that when i use relative reference address it’s going to copy and change so if i just leave it as it is now f5 will become f6 then it will become f7 and so on so i

Want it to point always to the same cell f5 so i’m going to make it as an absolute value and i’m just going to add the dollar signs we just press f4 and that’ll input the dollar signs converting this function this cell address into an absolute cell address so now i’m going to move to the next argument i’m going to type in comma which is argument separation side

Now what value i should have to demonstrate what happens if the first condition the logical test is true so my true value is actually for this purpose i’m going to use the present value formula which returns the present value of an investment okay so the total amount that a series of future payment is worth now so obviously this will also adjust depending on the

Year uh that we’re in all right so the first argument is going to be the rate and our rate is actually indicated it’s 4.125 and since we’re talking about not annual rate but monthly rate i’m going to divide this by 12. so i’m going to type f4 divided by 12. and now again if i start copying this formula uh in the for you know wrote year two year three and so on

If i don’t change this uh cell address to absolute cell address then it’ll transform it instead of saying uh showing it as f4 it’ll start showing f5 f7 and so on so i need to make it an absolute cell address so again press either either f4 or just type in dollar signs okay and once we’re done with this let’s move to the second argument for the present value

Formula all right so the next one uh is going to be a number of payments so it’s going to be f5 right f5 minus h5 so that’s how we’re going to determine the number of years still left and again because uh that’s 12 months in each year we’re going to multiply this difference by 12. so i’m just going to type f 5. minus h5 and multiply by 12. now notice that

I’m using f5 as an absolute cell reference and h5 as a relative the point being i always wanted to show this specific value so when i type f5 it always will go to this term number if number 15. now with each year this value should be changing so from year one that will convert to here two three so this is going to be a relative cell address and it will adjust

Coming from h1 then for this year it will become h i’m sorry h5 then for this here it’ll become h6 h7 and so on right so the next argument is actually going to be the monthly payment value okay so the third argument is payments it’s right here so the first one was rate then uh num uh p uh number of uh periods and then um periods for payments and now it’s just

The value of the payment so i can just indicate this value f6 okay and again this needs to be an absolute cell reference because it needs to stay put now one thing we need to remember when we created the formula for monthly payment we used pmt function and because it showed as a negative value because we actually owe the money we change the sign in front of the

Pmt function to negative so to reverse the sign of the function so it would show the positive value in the mortgage payment calculator so i’m just going to return to the original value and to do this i’m just going to enter a minus sign in front of the cell address so it’s going to be minus f6 as an absolute cell address and i’m going to close the parenthesis and

So now i’m done with my second argument for the if statement so this is the first one the logical test this is what happens what value i should display if the logical test is true and now once we reach year 16 this value the logical test becomes false and if this false that means that i don’t don’t owe bank any anything so this value will become 0 and i’m just

Going to type the last argument as zero and close parenthesis and so this is my ending balance for year one that means that this is how much i’m going to owe at the beginning of year two correct so basically uh beginning balance the actual loan amount minus the number of payment uh 12 payments um each payment consisting of this value all right so actually for

Uh the patent principle for k5 now we just have to show the difference between i5 and j5 so this is the difference between i5 minus g5 so that’s how much we paid on the principal while paying the monthly payment during the year one okay and for the interest date we’re now going to also use an if statement and they’ll just expand the cell so you can see it better

Right so i’m going to type in if and our logical test is going to be i’m going to check if my i5 value is actually greater than zero if it’s still greater than zero the beginning balance then i’ll keep paying if not then i’ll need to stop right and that means that i also don’t have any interest left so there won’t be any interest that we need to consider so i

Five greater than zero so that’s my logical test i’m just going to check okay if this is greater than zero then and i move to the second argument separated by a comma then actually the value in f6 the monthly payments times 12 so how much i’ll pay during the year so let’s type this in so f6 minus oh sorry multiply by 12 and again make sure that you use absolute

Value absolute style address for f6 because that’s always going to be in this very cell it can’t move anywhere else so if this again if our statement is true then uh monthly payment times 12 so that’s an annual payment minus k5 okay minus basically how much we paid on the principal and that concludes our second argument so this is just going to display the

Interest paid on the loan during the first year and so if this statement becomes false if the value in i5 is not greater than zero or equal or less then we don’t have any interest paid and i’ll just type as 0 for the third argument so now we need to figure out what the beginning balance for year two is going to be and obviously it’s just going to be the same as

The ending balance for year one which is located in j5 so i’m just going to type in equals j5 press enter and so now basically we are ready to start populating the other numbers and i’m just going to use the autofill function and just apply the rest of the data so in year 15 we actually see the beginning balance as 30 000 the ending balance as it should be at

Zero the principal paid 30 30 000 and the intro speed of 677 now the only things we have to calculate are subtotals down payment and total cost all right and so now we have to calculate the subtotals uh by using the autosum function so i’m just going to click on autosum press enter use function or autofill feature uh the down payment is obviously the same as

It was in cell d7 so i’m just going to type equals d7 and so our total cost is going to be the down payment plus how much we paid on principal k 20 and how much we actually paid on the interest l20 and so this number is exactly the same as we have in the mortgage payment calculator the total cost the total interest matches the value in l 20 and this is our

Subtotal how much we actually paid on the interest which exactly the value for the loan amount so this concludes the video cast about how to create the automatization schedule

Transcribed from video

Mortgage Payment Calculator #2 By Lana Short