 Home » SQL & PL/SQL » SQL & PL/SQL » Present value calculations in Oracle (12.1.0.1)
Present value calculations in Oracle Mon, 24 May 2021 04:13  OraFerro Messages: 415Registered: July 2011 Senior Member
Hi All,

I have the attached image example that describes a way of calculating Present Value for a loan.
(note: I could not upload the excel sheet due to upload policy but I am explaining my equations)

In the attached example a discount rate, loan interest rate, loan amount, and the number of equal bi-annual installments are known, and the calculations explain hot to calculate:
1- the present value of each installment (bi-annual).
[equation used: =B8/(1+\$B\$3)^A8 which is the installment amount/(1+bi-annual discount rate) ^ duration in years.]
2- the total present value of all installments.
[equation used: sum of column C]
3- the value of each interest (bi-annual).
[equation used: =D8*\$B\$5 which is the loan balance * Loan interest rate (bi-annual)].
4- the total present value of all interests
[equation used: sum of column F]
5- the present value of the loan (point 2 + point 3) above.
[equation used: sum of columns C and F]

I can try all the above in an SQL query but thought that there must be something easier in Oracle so, my questions are:
1- The above can be done using Excel function PV, is there an equal function in Oracle?
2- Is there a function is oracle that can take all known variables: (Loan amount, bi-annual discount rate, number of installments, bi-annual installment amount) and get the sum of all Installment PV (point 2 directly without calculating PV for each installment and the getting the sum).
3- Is there a function that can cover the above point and also take the loan bi-annual interest rate and get the final figure which is the PV of both Installment and Interest (point 5 above) without having to calculate each separately and then add them?

Thanks,
Ferro

• Attachment: Example_PV.PNG
Re: Present value calculations in Oracle [message #684397 is a reply to message #684396] Mon, 24 May 2021 04:34   Michel Cadot Messages: 68421Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

Where is the test case?
And don't forget to post, formatted, the result you want for the data you provide.

[Updated on: Mon, 24 May 2021 04:35]

Report message to a moderator

Re: Present value calculations in Oracle [message #684398 is a reply to message #684396] Mon, 24 May 2021 04:34 John Watson Messages: 8805Registered: January 2010 Location: Global Village Senior Member
Is this topic any help? It includes code at the end by Barbara, which is always good:

https://www.orafaq.com/forum/t/196472/

 Previous Topic: SUM of parent and child records Next Topic: Nested Queries with Select Statement
Goto Forum:

Current Time: Sat Apr 01 06:12:56 CDT 2023