An actuarial premium is money that is paid in regular amounts into an account at an estimated interest rate to reach a desired account total on a specific date. The amount of payments required on a monthly basis can be calculated quickly using a single formula in Excel as long as you have certain parameters. You’ll need to know the future value of the account, the estimated interest rate applied to the payments and any amount placed at the beginning of the calculation period to open the account.
Launch Microsoft Excel and click the "File” menu on the top toolbar. Select “New” and double-click “Blank Workbook.” This will create a new workbook to enter your formula without prior work cluttering your workspace or affecting the results.
Click the workbook cell in which you wish to display the premium payment amount. Type the payment formula “=PMT(rate,nper,pv,fv,type)” into Excel's insert-function box labeled "fx."
Click on the “fx” to open the Function Arguments window.
Type the anticipated yearly interest rate for your payments into the "Rate" box. Use percentage for the rate and divide the amount by 12 to establish that the rate is monthly. For example, if you anticipate a yearly interest rate of 3 percent, then type “3%/12” into the box. Excel will display the rate as “.0025” for the calculated premium.
Input the total number of payments made towards the future account amount into the “Nper” box. For a monthly premium, the total number of payments would be once a month up to the future date of the account. For example, if you are building an account to be used in 10 years, the number of payments would be 12 per year times 10 years, for a total of 120.
Enter the amount of any initial payment into the account in the “Pv” box to detail the present value of the account on the date of the calculation. If you open an account with $10,000, type "10000" into this box.
Type the account goal amount – the amount you wish to accrue with the monthly payments – into the “Fv” box. For an account worth $250,000, type "250000."
Complete the formula settings by typing a “1” or “0” into the “Type” box. A value of “1” indicates that the monthly premium is paid at the beginning of the month, while “0” indicates payment at the end of the month.
Click the “OK” button to calculate the annuity premium paid monthly towards the establishment of the annuity account amount. For an amount of $250,000 that’s ready at the end of a 120-payment period at a 3 percent yearly interest rate with an account opening amount of $10,000 and payments made at the beginning of each month, the displayed premium due is $1,880.88.