YouTube Description
Let’s build an Amortization Loan Payoff Schedule in Excel quick and easy.
Etsy Link to this Loan Payoff Schedule https://etsy.me/35hheEk
Making your own schedule isn’t hard and you can do it yourself. In this video we go over what loan details you need to input into Excel, the exact formulas need to calculate the principal and interest payments, and as a bonus you can track your age for each future period to know how old you will be when the loan is paid off in full.
Be sure to check out my website DugginMoney.com at https://bit.ly/DugginMoney
I’m a certified public accountant and I love to talk about anything money related. If you enjoyed this video be sure to subscribe. As always, thanks for watching.
Amazon Affiliate link
OSMO Action Camera https://amzn.to/3vvMYOt DJI OSMO Action on Amazon is my GoPro alternative
Crypto Referrals
Coinbase https://www.coinbase.com/join/duggin_2h Get $10 in Bitcoin Sign Up Bonus
Bittrex https://bit.ly/DugginMoneyBittrex Receive 10% Lifetime Referral Commission
Binance.US https://bit.ly/DugginMoneyBinance
Robinhood https://bit.ly/DugginMoneyRobinhood Get A Free Stock Signup Bonus
Webull https://bit.ly/DugginMoneyWebull Get A Free Stock Signup Bonus
Southwest Credit Card https://bit.ly/DugginMoneySouthwest Earn points to use towards Southwest purchases and flights
Duggin Money YouTube Channel https://bit.ly/DugginMoneyYoutube
YouTube Transcript
Today we’re going to be learning how to
make an amortization schedule in Excel
fast and easy and we’re going to get
right into it, here we go. If you are more
interested in downloading the
spreadsheet you can just go to my Etsy
Shop https://etsy.me/35hheEk and you can find a link to that in
the description below.
So open up Excel and we are going to get right into it.
Here we go.
You’re going to find on my template a
section for the loan administrator
information. It’s easy for not only you
to remember where everything is located
but also if other people need to come in
behind you and find where your website
login information is or family members
who need to also have this information
they can do that as well.
Right below that you’re going to see
the information for your loan detail.
We have 250,000 dollars hard-coded in here.
This isn’t a formula this is just
pulling it in as you type it and then
you can type in your annual interest
rate. You type in your loan period in
years and so this is for a 15-year
mortgage. If it was 30-year you would
type in 30 right here
or if it was a car loan it might be five
years and you would just type in five.
So we’re going to keep this at 15
and then we’re going to type in the
number of payments per year and so
that’s going to always be 12, typically .
You can also put in your origination date
and that origination date is just going
to be the date that you received the
loan. A lot of this detail is going to be
pulling down into our schedule.
This first column is for the month or
you can also refer to it as the period.
We got one two three four five and it’s just
going to be going down for each period.
And then we’ve got the date. This date is
pulling in from cell C11 which is our
origination date.
And then we’re going to be taking
a formula for the date and it’s going to
be able to take this B16 it’s going to take
this date that’s pulling in from our origination
date and then it’s going to be taking the
year from it the month
and we’ve got the day over here and so
what it’s going to be doing is going to
be taking this month from our previous
row and adding one to it. So that’s
what’s happening here. What you might
want to do is put in your payment date
as far as your first payment date right
here. That might help you kind of figure out
when this payment is due. We’ve got the
beginning balance.
It is a cell reference to C7 which is
our original loan amount
and then we start getting into the formulas.
Your total payment amount is going to be
your principal plus your interest and so
that is just adding up these two cells
for column E and column F.
Then the principal amount. So we have a
negative before our formula and it’s PPMT.
The reason we have a negative is so
that when it calculates it it’ll
bring it in as a negative. If you don’t
have this negative here and so if we remove this negative
these payment principal amounts will show as
negative and we kind of really just
visually don’t want that. We want just
more of a visual something that looks
better visually. So we’ll just leave that
in there. Let me double click that again, get back
into it. And so we have a dollar sign and
it’s referencing cell C8 and so that is so it doesn’t change as
you drag the formula down.
We have C8 which is the annual interest rate.
If I click here you can see in excel it shows you the formula
and it shows you what this actually is referencing.
This is the rate, this formula you want to have the
rate right there and it’s bringing in C8.
And ours is 3.75%
per annual so that’s per year and we’re
going to divide that by 12.
So that we get the monthly interest rate
and then this next one is the period and so it’s referencing
column A row 16 and it’s the first period.
And then this nper is right here
and this is going to be the total number
of periods that you have in your loan
and it’s referencing C9.
So C9 is 15 years times C10 the number of payments per
year and so that gives you the total number
of periods for your loan amount.
And then we have the present value which
is C7 of the loan which is the 250,000
amount that we have there
and then we have zero in the last to
close out the formula for future value which is zero.
Moving on we have the interest
and let’s look into this one as well. So
much like the previous formula for the
principle we have a negative
here to start it out and it’s IPMT for interest.
The other one was PPMT for principal
So IPMT. And we’ll just walk through this again.
It’s bringing in our rate and dividing by 12.
So it’s referencing C8 and again we have
dollar signs there so it doesn’t change which cell it’s
referencing as you drag your formula down.
And then the next one is A16 which is our
period. It’s also referencing this cell here for A16.
And then we have the total number of periods which is
just like our other formula.
It’s C9 which is 15 times C10 which is 12
and that’ll give us our total number periods.
And then for the present value it is also referencing
C7 and then the future value is zero
because hopefully you follow through and
actually make your payments and your
loan balance goes to zero.
So that would give us our interest
and I’ll come back to this extra
principle column but right now that’ll
give us our ending balance for this
first period and our ending balance is
just a formula. Going down it’s taking
our beginning balance,
it’s taking how much you paid in principle
and any extra principle you might have
paid and giving you your ending balance.
Let’s say on a month you have some extra
money and you want to send a payment in
you can just type in how much you’re sending in
and it’ll take into account how much extra you’re
sending in and it’ll reduce that ending balance.
So let’s say you receive an inheritance of a hundred
thousand dollars and you’re like you
know what I’m gonna send that to pay off my loan
balance and I don’t want to keep that in my bank.
Well you can just send that in,
put that in here and you can see how
that affects your loan payoff because as you go down
your loan payoff is going to be before
the end of your loan amount.
So if we look
here on period 119 we’ve got the date of when that is
and we also know that your last payment
is going to be 729 dollars and three cents.
And after that it’s going to be paid off.
Let’s go back up to the top.
Let’s remove these extra payments.
One thing I have over here that you
might like as well is whenever I have a loan payoff schedule
I like to know how old I’m going to be on these particular dates.
It’s like yeah you might already have an
idea but it’s nice to see how old you might be
and so you can type in your birth date right here.
This formula right here is going to take
your birthday and calculate how old
you’re going to be on this date.
And you can do that for your whole family.
Put them all in and kind of have
fun with it and that way as you scroll
down you’ll know how old you’ll be. So
one way to check that you have
everything inputted correctly is you can do some totals.
If you highlight a column, if you look down here
it gives you a sum.
That’s how much principle you’ve paid
off which happens to be our beginning loan amount.
If i highlight the interest column you
can see how much you’re paying interest.
So if we look on row two
right now we have this beginning balance.
It is actually just a cell reference
going to our ending balance and it’s
bringing that forward. So that we have a
new row of beginning what your payments
are going to be and
your ending balance.
And if I just kind of click down you can
see the cell reference up here in the formula bar.
It’s just updating to the new
ending balance from the previous row.
Then once you get those inputted you can
drag these down these formulas
and it’ll automatically update. So there’s a little
square over here in the corner if you put your mouse
cursor right on it and click that and then drag down
you can fill in all the rows that you want to fill in to
complete your schedule.
All right thank you for joining me on
this quick tutorial on how to build your
own amortization schedule in Excel and I
hope you enjoyed it and learned something new today.
If you made it this far in the video thank you so much I
appreciate it. If you would hit that
thumbs up and the subscribe button and
I will see you next time.
