Excel calculating interest rate on loan

Not sure if this is the right place to ask this type of question, apologies if it isn't.

I am following this explanation on office support to answer my own question.

Using the values they use, what would I change if payments were made at the beginning of each month, if any? Also, is the interest rate calculated the monthly or annual rate?

Thanks for any assistance

• Please include in the question the key parts of the linked web page, otherwise the question will be worthless if the link rots. Sep 4, 2017 at 12:14

The solution uses the PMT function which has the syntax:

``````PMT(rate, nper, pv, [fv], [type])
``````

where

Fv is Optional: 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.

and

Type is Optional: The number 0 (zero) or 1 and indicates when payments are due.

``````0 or omitted: At the end of the period
1: At the beginning of the period
``````

So `PMT(0.0058508,180,100000) = -900`

or `PMT(0.0058508,180,100000,0,0) = -900` both paying at the end of the period.

The periodic (monthly) interest rate is `0.0058508 = 0.0702095 / 12`

For the calculation with payments at the beginning of the period use

``````=PMT(B3/12,B2,B1,0,1)
``````
• Perfect! Thanks so much! Probably should have looked closer, when I did the =PMT(B3/12,B2,B1,0,1) it showed me what the 0 and 1 represented Sep 4, 2017 at 10:05