Home » SQL & PL/SQL » SQL & PL/SQL » Present value calculations in Oracle (
Present value calculations in Oracle [message #684396] Mon, 24 May 2021 04:13 Go to next message
Messages: 433
Registered: 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?


Re: Present value calculations in Oracle [message #684397 is a reply to message #684396] Mon, 24 May 2021 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68657
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous message
John Watson
Messages: 8934
Registered: January 2010
Location: Global Village
Senior Member
Is this topic any help? It includes code at the end by Barbara, which is always good:


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

Current Time: Fri May 24 11:32:19 CDT 2024