Formula Question

[former_member1267292]

In Business Objects, I have a date value, for example, 1/1/2016, in one column.  I have a 2nd column with an amortization term, for example, 24 months.  I am trying to set up a formula in a third column to return last payment date, which in this example would be 12/1/2017.

This appears to be straightforward, however, I was only partially successful in my efforts.

Any help would be appreciated.

Thank you.

[Former Member · Accepted solution]

What version of Webi are you on?

Also, is it always months that you are using?

If you're always using months, then you'd simply want to add n-1 months to your original date.

As of 4.1 SP6, the RelativeDate function has been enhanced to include other time periods than just days.

So, your final payment date would be, assuming that your in months:

=RelativeDate([Start Date];[Term]-1;MonthPeriod)

You could then substitute in WeekPeriod if the term is weeks and create some If logic as required based on your starting data.

The RelativeDate functionality has been restricted to days since it was introduced. The closest approximation that I've got working for adding months that you might want to try was:

=RelativeDate([Start Date]; ([Term]-1)*(365.25/12))

[former_member1267292 · Accepted solution]

When I use =RelativeDate([First Payment Date];[Amortization Term]-1;MonthPeriod)  - I get a missing operator or closing parenthesis in 'Relative Date' error.

When I go to 'About' to look at version, it shows SAP Business Objects Enterprise XI

Product: 12.1.0

Yes, always months.

Thanks

[Former Member · Accepted solution]

The additional parameter isn't available in your version.

The other option is to build something similar to what Brandon has suggested. Question - does Brandon's formula always give you the correct month?

[former_member1267292 · Accepted solution]

Looks like the day of the month is accurate in all my tests, year appears to be accurate in all my tests, but the month is accurate about 60% of the time. 

[Former Member · Accepted solution]

Question - if the start date is the 31st and the term finishes in September, what date should be shown as the finish date?

[Former Member · Accepted solution]

Have a try of this mouthful:

=ToDate(FormatNumber((Year([StartDate])*10000+(If(Mod([Term]-1;12)+MonthNumberOfYear([StartDate])>12; Floor(([Term]-1)/12)+1; Floor(([Term]-1)/12)))*10000)+(If(Mod([Term]-1;12)+MonthNumberOfYear([StartDate])>12; Mod([Term]-1;12)+ MonthNumberOfYear([StartDate])-12; Mod([Term]-1;12)+ MonthNumberOfYear([StartDate]))*100)+DayNumberOfMonth([StartDate]);"0");"yyyyMMdd")

[former_member1267292 · Accepted solution]

Mark, this seems to work.  Thank you so much for the help.

[Former Member]

Hi,

try something like this :

[initial date] = Start Date

[period] = Period in months

[end month] = Mod([period];12)

[end year] = Int([period]/12)

End_Date = ToDate([endMonth]+"/"+[end year]";"MM/yyyy), this would be the final result if you´re counting from December of Year 0.

But , as you´re counting from a specific [initial Month] [initial Year], the solution would be :

[final month] = if (([end month]+[initial month]) <=12 ; [end month]+[initial month]; [end month]+[initial month]-12)

[final year] = if (([end month]+[initial month]) <=12 ;[end year] + [initial year];[end year] + [initial year]+1)

The desired date [would be] =ToDate(""+[final month]+"/"+Day([initial date])+"/"+[final year];"MM/dd/yyyy")

Regards,

Rogerio

[Former Member]

The issue is Months are not exact so it will be difficult to get the same day of the month as your first pmt date to return.

This formula works, it's a bit of butchery though.

=ToDate(Concatenation(Concatenation(Concatenation(Concatenation(MonthNumberOfYear(RelativeDate([Scheduled First Payment];([Amortization Term]/12*365.25)));"/");DayNumberOfMonth([Scheduled First Payment]));"/");FormatNumber(Year(RelativeDate([Scheduled First Payment];([Amortization Term]/12*365.25)));"#"));"MM/dd/yyyy")

[former_member1267292]

Thanks Brandon.  When I tried this...it would return the correct value about 50% of the time.  I have about 20 different line items that I tested....

For example, had a first payment date of 1/1/16 with a 360 month amort - correct final payment date returned of 12/1/45.

Another had a first payment of 2/1/16 with a 360 amort - correct final payment date returned of 1/1/46.

However, another one had a first payment date of 8/1/13 with a 360 amort - the value generated was 8/1/43; I would have expected 7/1/43.

[Former Member]

I would try using the RelativeDate Function

ex =RelativeDate([Date Value];([Amoritization Term]/12*365))

The RelativeDate function uses days so you will have to convert. Also, you may need to wrap the amortization term in a ToNumber() function depending on how it is stored.

[former_member1267292]

Thanks.  When using the above formula, the day of the month isn't returning as I need.  For example, if the first payment date is 1/1/2016 and the amortization is 360 months, the last payment date should be 12/1/2045.  With the relative date function, the date returned is 12/24/45.

What I am using: =RelativeDate([Scheduled First Payment];([Amortization Term]/12*365))

Thanks

[amitrathi239]

Hi,

use this.

=RelativeDate([Scheduled First Payment];730))

Amit

[former_member1267292]

When I plugged in =RelativeDate([Scheduled First Payment];730) - with the scenario I mentioned above:

First Payment Date 1/1/2016

Amortization: 360 Months

The value returned is 12/31/17.

I need the value of 12/1/45 to return- which would be the last payment made.

As another example, if the First Payment Date is 1/1/2016 with a 240 month amortization, I would need 12/1/35.

I am not well versed on the various functions at all; not sure what the '730' is doing in your suggestion.

Thanks for the help.