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

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.

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. We also provide freelancing services on the aforementioned topics. Feel free to reach out to us for any kind of discussion on any of the related topics.

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

  1. Needed to create you this very small remark to help say thank you once again for the unique principles you’ve shown on this site. It was really surprisingly open-handed of you to convey extensively exactly what most people would have offered for sale as an e-book to end up making some dough on their own, specifically since you could possibly have done it in the event you desired. These principles additionally worked like a fantastic way to recognize that many people have similar dreams like mine to learn way more with regards to this problem. I think there are many more pleasurable moments in the future for people who browse through your site.

Comments are closed.