Amortization in Notes DB

Yes.
In the column field use a formula to take the value and divide it by 12.

Make sure you use a number. If it is a string, convert to a number first.

You may need to use a temp value also.

I will look for sample.

I hope this helps !

Sample

REM "If modified inside 10 Minutes still consider Entry as New!";
Diff := @Modified - @Created;
Status := @If(Diff > 600; "Updated";"New");
@If(Status ="Updated"; Status + " - " + @Subset(@Name([CN];$UpdatedBy);-1); Status)


you should simply try

AMort=Amotrfield/12;

or simialar.

I will look for a beeter sample.

Hi,

I  can simply divide by 12, but the part that i'm trying to figure out how to do is, get a rolling sum each month following the "start date",
so ex.

if record 1 start_date = 8/24/2006  price = "$144.00"

in form or view( if record is amort then (, August Rev = 12, Sept Rev = 24, Oct rev = 36, etc.... ), else if not amort then Aug Rev = 144)
 my top goal is trying to get the Amort peice working right now..

I hope that makes sense, thanks in advance for any assistance.

Update:

So each month would be  $12.00, but be able to have a Total field that will continue to sum as the months pass.  

Sorry. Still not clear.

I need specific expample of data, and how you want it to look in the view.

ok,

Customer 1 purchase waranty plan  $144.00 January.  
Customer 2 purchase waranty plan $288.00 June.
Customer 3 purchase waranty plan $72.00 August.

Regardless of Dollar amount, Waranty plan is for 12 months.  Revenue from customer is recognize monthly (dollar amount/12).  

Customer 1 pays $12 per month for 12 months
Customer 2 pays $24 per month for 12 months
Customer 3 pays $6 per month for 12 months
In view, revenue recognize
2006
Jan - $12 total (12 for cust1)
Feb - $12 total (12 for cust1)
Mar - $12 total (12 for cust1)
Apr - $12 total (12 for cust1)
May - $12 total (12 for cust1)
Jun - $36 total (12 for cust1 + 24 for cust2)
Jul - $36 total (12 cust1 +24 cust2)
Aug - $42 total (12 cust1 + 24 cust2 + 6 cust3)
Sep - $42 total (12 cust1 + 24 cust2 + 6 cust3)
Oct - $42 total (12 cust1 + 24 cust2 + 6 cust3)
Nov - $42 total (12 cust1 + 24 cust2 + 6 cust3)
Dec - $42 total (12 cust1 + 24 cust2 + 6 cust3)
Jan 07 $30 (24 cust2 + 6 cust3)  (0 from Cust1, expired)
Feb 07 $30 (24 cust2 + 6 cust3)

an so on and so on.. please let me know if this makes sense

OK, now it make sense.

Now, do you want just a monthly summary as shown or do you want a breakdown  showing the details and at the end, the monthly sum.

In any case, you will need to access the Date the customer started his warranty.

Is this for one year oly ( meaning Warranties that were done just in 2006 ), or are there older ones as well ( 2005 ) that need to be considered, since it runs for 12 months ?

I would create a view  sorted by start of warranty in col 1.

In col2 would be the amount /12

I would then add a summary for the 2nd column by change in month.

Then I would limit the view to only the LAST 12 months !!

that should do it.

I hope this helps !

I have the start date, the amount field,

In view i have col1 as start date, Col2 i have amout/12, with total.   But all this does is gives me the three start dates in col1, and  three amount/12 in col2 with a total.  

What i was looking for is how to calculate if the cust has expired (start_date +12 months), if it is not expired and then take amount/12.  with the summary, I will total up that 1/12 portion  for  the records that is not expired in that month

Why not just eliminate anything older than 12 months from the view. Add a date limitation in the View selection.
Change the first column to be just the Month portion of the start date.

This will give you a summary by month of all customers < 12 months old.

I hope this helps !

ok, that makes sense, I can eliminate customers that are expired.  I'm going to try the view with the month of start_date categorized.  but I'm thinking that it would only give me one summary of 1/12 revenue from all customers by month of their start date as of today.

What I was looking for is something similar to this but accross the 12 months of their contracts, as some contracts will expire and/or start before or after others.  So I would be expecting varying amount of dollars total each month.  

I was thinking about something like this.  
@If((@Today-start_date)/86400>366;"";price/12) ---> then have a total.  
Now this works to capture 1/12 as it stands today.  But I need to figure out how to get it to be identified into a column based on a month.   and spread across the months of the year and so onward...

I don't know if that makes sense?

You need to total your total monthly column so that you can have a running total, not just per month.

Make the first column categorized, stardate, but just the Month portion of it.

Use @Month(startdate)
See also the help for @month.

I'm using:

col1: @month(start_date) - sorted A
col2: @If((@Today-start_date)/86400>366;"";price/12) - Totaled

Using same scenario as above:

Customer 1 purchase waranty plan  $144.00 January.  
Customer 2 purchase waranty plan $288.00 June.
Customer 3 purchase waranty plan $72.00 August.

This is my result:

Col1     Col2    
****|*****
1      |12
6

Col1     Col2    
******|*****
1          |12
******|*****
6          |24
******|*****
8          |6
******|*****
*total*  |42

This is just totaling 1 time based of the month of the start date.  I'm imagining something more of:

Col1(Cust_Num), Col2(Jan06), Col3(Feb06).....Col7(Jun06), Col8(Jul06), Col9(Aug06), Col10(Oct06), etc
Cust1               | 12             | 12             |....| 12           | 12            | 12             | 12               |....
***********|*********|*********|**|********|********|*********|**********|**
Cust2               | 0              | 0                |....| 24           | 24            | 24             |24                |....
***********|*********|*********|**|********|********|*********|**********|**
Custt3              | 0              | 0                |....| 0             | 0             | 6              | 6                  |....
***********|*********|*********|**|********|********|*********|**********|**
*Total*            | 12            | 12              |....| 36            | 36            | 42            | 42               |....

or something that will show a monthly summary, across the life time of the 12 months.  I want to eventually recognize the entire amount.  But I would like to go into a month and see how much i am going to or is recoginizing from all customer for that month.

You will need to create additional columns.

You have to decide whether you want to start in January, or the last 12 months 9 sep 05 -> sep 06 ).

Also, do you want to show the customer info or just the monthly total ?

I would put the

@If((@Today-start_date)/86400>366
in the View selection and not in the column.
The columns should be based on just the start date - Check if Month >- 1 for Jan col,
Month >=2 for Feb column etc,

I hope this helps !

Hi,

Thank you for your help.   I ended up using the end date, calculated from the start_date, as the driver for each column.   I have 12 spanning columns using formulae:
@If(@Adjust(start_date;0;+12;0;0;0;0)  >=@Date(2006;2;1) & (@Adjust(start_date;0;+12;0;0;0;0))  <=@Date(2007;1;31);amount/12 ;0).  I was able to use this formula to get my columns workings.

On another note, would you happen to have any sample of lotus script that I can use:

I'm trying to find something that would  read each line of a import file, and then lookup that customer document and update the appropriate field(s)

Please open a new question so that others may also contribute.

I do have all sorts of import procedures. It depends on what you need.