Accounting app - expenses amortized

[gerben]

Hi all,

I’m currently building an application that has to manage assets for a company. However, the company can amortise/depreciate the assets (e.g. over 5 year, over 10 year,…).
At this moment, I’m a bit stuck on how to proceed with this. I want to save the “years” in which they have to depreciate the amounts (e.g. 2022 - 2023 - 2024 -…) and the amount that has to be depreciated (e.g. $1000 over 5 years, meaning $ 200 every year for years 2-3-4, however for year 1 and 5, it has to take into account a pro-rata basis (based upon the number of days in the year)). For an example, refer to here below:

Could you guys help me on how to save this to my database?
Any help would be very much appreciated!!

Gerben

[gerbertdelangen]

@gerben, you probably do not need to save the years and amounts. You have the investment amount and purchase date already, just add a field “number of years to depreciate” to that same data type to save the number of years the user entered in field “Number of years / Aantal jaren”. With this information you can always recalculate the amounts to depreciate per year. Also, when the depreciation entry is made you can easily prefill the amount field with the calculated depreciation amount for that year.

And if it is possible to differ from the proposed depreciation amounts when making the entry, you might need to take into account the depreciation entries made for that investment in previous years when you calculate what amount still needs to be depreciated.

[pfthiessen]

Hi Gerben,

for this I think you would need to look at a backend workflow to iterate over a list of data, this would generate a new entry for each year, and should be able to work within your current setup.

For your data setup you might need to add a starting amount for each year, to take into account prior years depreciation to make sure that you end up with a workable schedule.

For the setup of the recursive workflow, I think you can use the number of years as the iterator which would tell the workflow how many times to iterate.

How to Run a Loop in Bubble.io - YouTube This video gives a good explanation on how to run these.

[gerben]

Thanks for the input @gerbertdelangen & @pfthiessen!

I tried both of you guys’s approaches and it seems that I do have to save the data (year, amount depreciated per year,…) in my database. I managed to do so, by using the iterations and saving the years (boekjaren) depending on the depreciation period (duur afschrijving).

The current issue I’m facing is that I want to save the amounts of depreciation per year (currently it only works for the first year -on pro rata basis- and second year, but I can’t manage to save the depreciation for the subsequent years, as you can see in the first column).

Moreover, afterwards, I would like to select for example the year (boekjaar) 2022 and only see the depreciation amount for that year on the front-end of my app. Would that be possible?

Thanks for your valuable feedback!

Cheers,
Gerben

[equibodyapp]

@gerben depreciation has a start date (i.e date asset was purchased or commissioned) and an end date (i.e start date + useful life or when it was sold/decommissioned).

So you would have the user enter the start date and then the years to be amortised (in Australia we call this the ‘useful life’). From this you would actually calculate the days to be depreciated over. This way you can then at any point use the calculation between relevant dates to produce the depreciation amount with appropriate pro-rata without needing to actually have those numbers in your database.

[gerbertdelangen]

@gerben

Here is an example of how to do this:

If this example works for you it might be good to see wether all data, steps and custom states are good, needed or maybe things can be removed / combined, rounding differences can be prevented etc. The step after that would be to not use the data type value calculation, but to use a custom state list (type text) in which we save the start date, end date, nr of days and amount, together with the simple looper plugin this means we can do everything client side and prevent saving unnecessary data like i wrote in my initial post. The repeating group will have this custom state list as data source.

Let’s start:

1) Two data types, investment and value calculation:

2) The page and elements

A page with inputs for start date, nr of years and amount inside of a group + a repeating group to show the calculations and a button to start the calculation. Below these elements a repeating group to show the calculations linked to the investment.

3) Plugin Simple looper

To not have to use a backend workflow for looping, I used the plugin Simple looper. Install the plugin and place the Simple looper element on the page:

4) The button calculate triggers this workflow:

Step 1: Delete previous calculations related to this investment:

Step 2: Set a state calc_year to keep track of which year we are performing the calculation for and a state end_year to be able to check until which year.

Step 3: Calculate hte number of days between the selected date and the selected date + nr of years.

Step 4: Calculate the amount of amortisation /depreciation per day:

Step 5: Start the loop

5) The workflow which is looped

a) Step 1 to 6 are to create the value calculations:

• step 1 and 2 are for the first year
• step 3 and 4 for are for the years between the start year and last year
• step 5 and 6 are for the last year

b) Step 7: Add 1 to calc_year, this is used for the value calculation and also to know when to stop the loop.

c) Step 8: A check to see wether hte loop needs to be continued or stopped.

6) The result:

The result after pressing the button Calculate

*2024 is a leap year.

Good luck!

[gerben]

Hi @gerbertdelangen

Thanks a lot for your detailed explanation! Until now it works, however it keeps creating the items. I think that is because I cant use the brackets () in my formula. Could you explain maybe how I can add these and I’ll try again! Thanks!

Kind regards,
Gerben