# Macro For Present Value of Annuities ( Level, Increasing and Decreasing- Both Advance and Arrear)

0

Pre-requisites for understanding the model:-

• Basic Knowledge of VBA such as Conditional statements, Loops and Arrays
• Knowledge of CT 1

The Code below is to calculate the present value of Increasing, Decreasing and Level Annuity (Both Advance & Defer).

Sub PV_Of_Annuities()

`Dim A() As Double, B() As Double, C() As Double, D() As Double, DR As Double`

`DR = Cells(2, 2) + 1Size = WorksheetFunction.CountA(Range("A:A")) - 1`

`For i = 1 To Size`

`ReDim A(Cells(i + 1, 1)), B(Cells(i + 1, 1)), C(Cells(i + 1, 1)), D(Cells(i + 1, 1))`

`For j = LBound(A) To (UBound(A) - 1)`

`A(j) = 1 / DR ^ jB(j) = 1 / DR ^ (j + 1)C(j) = Cells(i + 1, 3) + Cells(i + 1, 4) * jD(j) = Cells(i + 1, 10) - Cells(i + 1, 11) * j`

`Next`

`Cells(i + 1, 5) = WorksheetFunction.Sum(A) * Cells(i + 1, 3)Cells(i + 1, 6) = WorksheetFunction.Sum(B) * Cells(i + 1, 3)`

`Cells(i + 1, 7) = WorksheetFunction.SumProduct(A, C)Cells(i + 1, 8) = WorksheetFunction.SumProduct(C, B)`

`Cells(i + 1, 12) = WorksheetFunction.SumProduct(D, A)Cells(i + 1, 13) = WorksheetFunction.SumProduct(D, B)`

`Next`

`End Sub`

Using the above code, one can calculate the Present Value of different types of annuities in a few seconds just by clicking a button.

Do let us know your views on the same. Any kind of suggestion or feedback is most welcome.

Our next posts will include macro for Expected Present Value of Annuities, Assurances and etc.

0 ### Mathematica-City

Mathematica-city is an online Education forum for Science students run by Kounteyo, Shreyansh and Souvik. We aim to provide articles related to Actuarial Science, Data Science, Statistics, Mathematics and their applications using different Statistical Software. Feel free to reach out to us for any kind of discussion on any of the related topics,