Author Archives: PR

IPMT Financial Function Excel 2010

The IPMT function allows you to work out the Interest payment based on the set criteria as outlined in the syntax below.

Syntax

IPMT(rate,per,nper,pv,fv,type)

Rate     is the interest rate per period.

Per     is the period for which you want to find the interest and must be in the range 1 to nper.

Nper     is the total number of payment periods in an annuity.

Pv     is the present value, or the lump-sum amount that a series of future payments is worth right now.

Fv     is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

Type     is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

The first thing you might want to do is set up your spread sheet and format accordingly.  This is an example of how I would set up my spreadsheet and what it will end up looking like. Please note on my example I have used both the PPMT and the IPMT functions.  I have added a short blog on the PPMT function on this site.

In the cell to the right of the INTEREST COMPONENT where you see “£2,479.17” is where you will enter your IPMT function.  This is what it will look like if you use your INSERT FUNCTION option;

You will notice that way I have suggested you set up your spread sheet mirrors the options shown in this box and that makes entering the correct information a pretty simple process.   Two things you need to remember are;

  1. to note is that you have to divide the rate by 12 months, so it will look something like this “B3/12
  2. enter the PV as a negative as this is the amount of money that is being paid back, ie, if you borrow £500,000 for a mortgage then this is the amount of money that you are in debt for.

This completed function will look like this;

On your screen the function will appear like this;

PPMT Financial Function in Excel 2010

PPMT Financial Function in Excel 2010

The PPMT function allows you to work out the principal payment based on the set criteria as outlined in the syntax below.

Syntax

PPMT (rate,per,nper,pv,fv,type)

Rate     is the interest rate per period.

Per     specifies the period and must be in the range 1 to nper.

Nper     is the total number of payment periods in an annuity.

Pv     is the present value — the total amount that a series of future payments is worth now.

Fv     is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type     is the number 0 or 1 and indicates when payments are due.

The first thing you might want to do is set up your spread sheet and format accordingly.  This is an example of how I would set up my spreadsheet and what it will end up looking like. Please note on my example I have used both the PPMT and the IPMT functions.  I have added a short blog on the IPMT function on this site.

 

In the cell to the right of the PRICIPAL COMPONENT where you see “£3,190.42” is where you will enter your PPMT function.  This is what it will look like if you use your INSERT FUNCTION option;

 

You will notice that way I have suggested you set up your spread sheet mirrors the options shown in this box and that makes entering the correct information a pretty simple process.   Two things you need to remember are;

  1. You have to divide the rate by 12 months, so it will look something like this “B3/12
  2. Enter the PV as a negative as this is the amount of money that is being paid back, ie, if you borrow £500,000 for a mortgage then this is the amount of money that you are in debt for.

This completed function will look like this;

 

while the funtion on the screen would look like this;

 

 

 

 

 

 

 

 

 

 

 

Get the lastest currency rates automatically updated in your spreadsheet!

Get the lastest currency rates automatically updated in your spreadsheet!  And they use the data within charts, functions and more…

Select the DATA tab on your excel 2010 ribbon and select EXISITING CONNECTIONS

Select the option MSM MONEYCENTRAL INVESTOR CURRENCY RATES

Its as easy as that! Give excel a few moment and you will have the lastest currency rates at your fingertips!  Just like the example below!