Something which affects most of us is paying or receiving interest, whether its a bond, car loan or money you might have in the bank.
Excel has many built in formulas and quite a few for financial calculations.
For the purposes of this post, we will us the PMT function. - this will calculate a regular payment amount on a fixed amount of money at a fixed interest rate over a certain period of time.
Open Excel, pick a cell and type in =PMT(
A popup will show the following: =PMT(rate, nper, pv, [fv], [type])
each of these words are numbers that form part of the interest calculation.
rate = this is the interest rate for each period of the loan (i.e. each month) - this can be tricky if interest rate is know for the year but you have to calculate it monthly - all you have to do is divide by 12
nper = number of periods
pv = present value of loan
[fv] and [type] are more advanced and optional and we will leave them out for now.
for purposes of simplicity, lets work out what the monthly interest payments would be on an 100,000 loan at an interest rate of 10% over 5 years.
rate = this would be 0.10 (10%) divided by 12 to get the monthly rate
nper = 5 years * 12 months = 60 periods
pv = 100,000
in Excel, type the following:
=PMT(.10/12, 60, 100000)
you should get a figure of -2124.70 - (this may be shown in $ depending on how regional setting are set up)
Excel has many built in formulas and quite a few for financial calculations.
For the purposes of this post, we will us the PMT function. - this will calculate a regular payment amount on a fixed amount of money at a fixed interest rate over a certain period of time.
Open Excel, pick a cell and type in =PMT(
A popup will show the following: =PMT(rate, nper, pv, [fv], [type])
each of these words are numbers that form part of the interest calculation.
rate = this is the interest rate for each period of the loan (i.e. each month) - this can be tricky if interest rate is know for the year but you have to calculate it monthly - all you have to do is divide by 12
nper = number of periods
pv = present value of loan
[fv] and [type] are more advanced and optional and we will leave them out for now.
for purposes of simplicity, lets work out what the monthly interest payments would be on an 100,000 loan at an interest rate of 10% over 5 years.
rate = this would be 0.10 (10%) divided by 12 to get the monthly rate
nper = 5 years * 12 months = 60 periods
pv = 100,000
in Excel, type the following:
=PMT(.10/12, 60, 100000)
you should get a figure of -2124.70 - (this may be shown in $ depending on how regional setting are set up)
