Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Annuity Calculations

RSS
Modified on Tue, Jun 27, 2023, 5:03 AM by Administrator Categorized as Algorithms, General Information, Microsoft Office

Overview

This article explains various means of calculating the payment amount of an annuity given the present value and interest rate.

IMPORTANT NOTE REGARDING INTEREST RATES AND APR

1. In the following formulas, the interest rate is the PERIODIC rate. Thus, if N is in months, then the interest rate should be PER MONTH.

2. Banks typically calculate APR as COMPOUNDED YEARLY. Thus if N is in months, then the interest rate you use should be APR/12.


Solutions

In Excel

Annuity Calculation in Excel

Annuity Calculation in Excel


In Visual Basic

Function AnnuityToPvRatio(i As Double, N As Integer) As Double

    Dim fp As Double
    fp = FutureToPvRatio(i, N)
    
    AnnuityToPvRatio = (i * fp) / (fp - 1)

End Function
Function FutureToPvRatio(i As Double, N As Integer) As Double

    FutureToPvRatio = (1 + i) ^ N

End Function

Manual Calculation

         i (1+i)^N     
A/P = ---------------    
        (1+i)^N - 1


         i (F/P)      
     = -------------    
        (F/P) - 1

        
Solving for N,

     -log(1 - PI/A)
N = ----------------
       log(1 + I)

SQL Server Function

create or alter function dbo.AnnuityPaymentQty(
     @apr decimal(20,10) 
    ,@p   decimal(20,10) 
    ,@a   decimal(20,10) 
) returns int as begin

/*-------------------------------------*/
/*
declare
     @apr decimal(20,10) = 0.08
    ,@p   decimal(20,10) = 10000
    ,@a   decimal(20,10) = 202.76
    -- Expected result: 60
*/
/*-------------------------------------*/
declare @interest decimal(20,10) = @apr / 12.0

declare @n int = -log(1-(@p*@interest/@a)) / log(1 + @interest)

return @n

end
  Name Size
- AnnuityExcel.png 7.10 KB

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.