Skip to Main Content
  • Questions

  • Compound Principle Amount and Profit Calculation

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tanveer.

Asked: January 16, 2017 - 7:54 am UTC

Last updated: January 19, 2017 - 1:10 am UTC

Version: 10.2.0.1.0

Viewed 1000+ times

You Asked

Hi Tom,
Hope you are at the best of you health.

I have following table structure with sample data.

CREATE TABLE investments
(
investment_date DATE,
registration_no VARCHAR2(100),
amount NUMBER,
profit_rate NUMBER,
maturity_rate NUMBER
maturity_date DATE,
institute_name VARCHAR2(100) )

and sample data is following.

INSERT INTO investments
VALUES ('25-MAR-2013', 'L-851845', 1900000, 9.7, 10.3, '25-MAR-2016', 'National Saving Centre' );

I need to construct a SQL in order to generate a group of rows with profit calculation like following:-

Period Start Profit_Rate Amount No of profit Period Ending Profit (6 Monthly)
============ =========== ========== ============ ============= ==================
25/03/2013 9.7 1,900,000 1 25/09/2013 92,150
26/09/2013 9.7 1,992,150 2 25/03/2014 96,619
26/03/2014 9.7 2,088,769 3 25/09/2014 101,305
26/09/2014 9.7 2,190,075 4 25/03/2015 106,219
26/03/2015 9.7 2,296,293 5 25/09/2015 111,370
26/09/2016 10.3 2,407,663 6 25/03/2016 123,995

column maturity_rate will be used for calculation of profit at maturity otherwise use column profit_rate.

The formula we are using to calculate half yearly profit is (amount * (profit_rate/100) * .5)


and Chris said...

You can use a couple of tricks to do this:

- Use a row generator to create the rows for each month
- Take advantage of the fact that:

product ( 1 .. n ) ~ exp ( sum ( ln ( 1 .. n ) ) )


Combine these and you can get results like those you want:

CREATE TABLE investments (
investment_date DATE,
registration_no VARCHAR2(100),
amount NUMBER,
profit_rate NUMBER,
maturity_rate NUMBER,
maturity_date DATE,
institute_name VARCHAR2(100) 
);

INSERT INTO investments 
VALUES ('25-MAR-2013', 'L-851845', 1900000, 9.7, 10.3, '25-MAR-2016', 'National Saving Centre' );

with dates as (
  select add_months(date'2013-03-25', (level-1)*6) dt, i.*,
         1 + (profit_rate / 100 * 0.5) return_rate
  from   investments i
  connect by level <= months_between(maturity_date, investment_date)/6
), totals as (
  select d.dt, return_rate, amount, 
         add_months(investment_date, (rownum-1)*6)+decode(rownum, 1, 0, 1) pstart,
         add_months(investment_date, (rownum)*6) pend,
         nvl(round(amount * exp(sum(ln( return_rate )) over 
           (order by dt rows between unbounded preceding and 1 preceding))), amount) amt
  from   dates d
)
  select pstart, pend, amt, lead(amt) over (order by dt) - amt profit_6_mth
  from totals t;

PSTART                PEND                  AMT        PROFIT_6_MTH  
25-MAR-2013 00:00:00  25-SEP-2013 00:00:00  1,900,000  92,150        
26-SEP-2013 00:00:00  25-MAR-2014 00:00:00  1,992,150  96,619        
26-MAR-2014 00:00:00  25-SEP-2014 00:00:00  2,088,769  101,306       
26-SEP-2014 00:00:00  25-MAR-2015 00:00:00  2,190,075  106,218       
26-MAR-2015 00:00:00  25-SEP-2015 00:00:00  2,296,293  111,370       
26-SEP-2015 00:00:00  25-MAR-2016 00:00:00  2,407,663


Note the exp/ln trick isn't exact, so you may have rounding errors...

You can read more about row generators at:

https://blogs.oracle.com/sql/entry/fizzbuzz

And the exp/ln trick at:

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9531828400346596678

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

10g MODEL Clause

Rajeshwaran, Jeyabal, January 18, 2017 - 12:45 pm UTC

Since this question came up from Oracle 10g database, I would pick the advantage of SQL Model clause to answer this.
demo@ORA11G> select new_period_start,new_profit_rate,new_amt,no_of_profits,new_period_end,new_profit
  2  from investments
  3  model
  4    dimension by (0 x)
  5    measures( investment_date, amount, profit_rate, maturity_rate, maturity_date ,
  6            cast(null as date) as new_period_start,
  7            0 as new_profit_rate,
  8            0 as new_amt,
  9            0 as no_of_profits,
 10            cast(null as date) as new_period_end,
 11            0 as new_profit )
 12    rules iterate(1000) until( add_months( investment_date[0], 6*(iteration_number+1) ) >= maturity_date[0] )
 13        (     new_period_end[iteration_number] = case when cv(x)=0 then
 14                                        add_months( investment_date[cv(x)],6  )
 15                                        else add_months( new_period_end[cv(x)-1],6 ) end ,
 16              new_period_start[iteration_number] = case when cv(x)=0
 17                                    then investment_date[cv()]
 18                                    else new_period_end[cv()-1]+1 end ,
 19              new_profit_rate[iteration_number] = case when cv(x)=0
 20                                    then profit_rate[cv()]
 21                                    when add_months( investment_date[0], 6*(iteration_number+1) ) >= maturity_date[0]
 22                                    then maturity_rate[0]
 23                                    else new_profit_rate[cv()-1] end ,
 24              new_amt[iteration_number] = case when cv(x)=0
 25                                    then amount[cv()]
 26                                    else new_amt[cv()-1] + new_profit[cv()-1] end ,
 27              no_of_profits[iteration_number] = iteration_number+1,
 28              new_profit[iteration_number] = case when add_months( investment_date[0], 6*(iteration_number+1) ) >= maturity_date[0]
 29                                      then round(new_amt[cv(x)] * ( maturity_rate[0]/100 ) * 0.5)
 30                                    else round(new_amt[cv(x)] * ( new_profit_rate[cv(x)]/100 ) * 0.5) end )
 31  order by new_period_start ;

NEW_PERIOD_ NEW_PROFIT_RATE    NEW_AMT NO_OF_PROFITS NEW_PERIOD_ NEW_PROFIT
----------- --------------- ---------- ------------- ----------- ----------
25-MAR-2013             9.7    1900000             1 25-SEP-2013      92150
26-SEP-2013             9.7    1992150             2 25-MAR-2014      96619
26-MAR-2014             9.7    2088769             3 25-SEP-2014     101305
26-SEP-2014             9.7    2190074             4 25-MAR-2015     106219
26-MAR-2015             9.7    2296293             5 25-SEP-2015     111370
26-SEP-2015            10.3    2407663             6 25-MAR-2016     123995

6 rows selected.

demo@ORA11G>

Chris Saxon
January 18, 2017 - 2:59 pm UTC

Yes, you could use model too.

Tanveer, January 18, 2017 - 3:25 pm UTC

Thank you.
Connor McDonald
January 19, 2017 - 1:10 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.