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

0

Please Download the input data and the output file from the below given Download Button.

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) + 1
Size = 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 ^ j
B(j) = 1 / DR ^ (j + 1)
C(j) = Cells(i + 1, 3) + Cells(i + 1, 4) * j
D(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,

Leave a Reply

Your email address will not be published.