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.
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.