I have recurring sales invoices for rents. They produce each month an invoice.
However once every year the rent should be increased of a percentage equal to the national inflation. Every rent increases on the subscription day of each year.
Is there a way to automate this?
No. The easiest thing would be to edit each recurring invoice and enter a calculation in the unit price field to multiply the old unit price by the escalation percentage. Unfortunately, Batch Update does not work for recurring sales invoices.
I thought to use a fictitious secondary currency but without success since I should use one for each contact.
Sounds like an ingenious plan! I’d recon it should work, but why would you use a separate currency for each contract?
Seems to me like you keep:
- 1 nominal currency (C),
- 1 inflation adjusted currency (C infl.),
- 1 clearing bank account for the inflation adjusted currency
… and do a kind of a “real estate developer” setup.
Your initial 0-value receipt entry upon signing of a contract will have the following lines:
||Amount (C infl.)
|Unearned contracts > Customer 1a
|Contracts receivable > Customer 1b
…where (X) amount is the full value of the contract measured at the inflation adjusted currency (C infl.). This would also mean that the receivable is fixed from the get go.
You can title your receipt “Opening of Contact” and you should have no problem explaining a 0-valued receipt to your auditors or tax authorities – unlike a similar setup using invoices.
Later you issues your recurring sales invoices to your Customer 1a in Unearned contracts and record actual receipts against your Customer 1b in Contracts receivable.
I have used this setup before but not using a foreign currency, so there might be some rough edges to be rounded here and there.
That seems cumbersome to me. You would have to create a new inflation-adjusted currency every year. And the customers would not be denominated in it. The potential for losing track of things seems very high. To me, the answer seems to be adding Batch Update capability to recurring transactions. I don’t know why it is not already there. Surely there must be a reason, since it’s available so broadly within the program.
Yes, it is cumbersome, but sometimes you have go the extra mile just to accommodate a non-standard situation.
Not really, you just have to update your exchange rates.
However, if you have distinct categories of inflation adjusted rates, you might need separate currencies for that, i.e:
- C 6-month infl.
- C 1-year infl.
- C 2-year infl.
That’s could also work but, for me, I find comfort in knowing that everything has been determined right from the start.
Also, it could* work with
Journal Entries and then you could then to get away without a clearing bank account but I feel that these are way too restricted to be useful.
As far as possible mistakes, you can do the following to keep things honest:
All transactions in the clearing account must add up to Zero and therefore, the entire balance of that account must add up to Zero at all times, by extension. So any transaction that has an amount is an automatic red flag.
You setup recurring invoices immediately upon signing of contract at fixed amounts in (C infl.).
You can relabel accounts to make them clearer, e.g:
||Contracts to be invoiced
||Customer 1 (to invoice)
||Customer 1 (receivable)
- The neat part is any unaccounted differences will be automatically cleaned up by Manager as Foreign exchange differences.
So what you should really worry about is this:
- Set things up correctly – which is a one time exercise at the beginning of the contract. This includes the 0-value receipt and recurring invoices.
- Ensure the clearing account balance as well as its individual transactions are Zero.
- Record receipts under the correct account.
These three points don’t look like much to me but everyone has his different assessment.
I think I would rather just edit the recurring transactions. Possibly tedious, but straightforward.
Because you adjust each contract after one year from the signature not at a fixed date, ie for examevat 1st january of each year. So if you sign one at 3rd February and one on 25th October you will have two different adjustments
Maybe @lubos can add inflation to recurring invoices.
Since the receivable amounts will be fixed in inflation adjusted currency once you create a 0-valued receipt, the can all share the same currency and:
the contract starting on 1st Jan would reflect 1st Jan exchange rate.
the contract starting on 3rd Feb would reflect 3rd Feb exchange rate.
the contract starting on 25th Oct would reflect 25th Oct exchange rate.
The main advantage here is that initial receivable inflation adjusted amount will never be affected by future exchange rates.
Also any effects on later exchange rate changes on later invoices and actual receipts will be automatically calculated by the program.
In some respects, @Ealfardan and @Davide, I think you are talking past each other. @Ealfardan seems focused on contractually specified rent increases that are known in advance. @Davide seems to be thinking of rent adjustments in response to inflation that are not predictable in amount.
That flew right over my head. Sorry @Davide
Since you know how long the contract is and how much it will increase with. Why not setup all your recurring periods upfront. For example Tenant A signs a 3 year lease in Feb 2022. You recurring periods will be as follows:
- 01 Feb 22 - 31 Mar 23
- 01 Feb 23 - 31 Mar 24
- 01 Feb 24 - 31 Mar 25
Yes but… I cannot know before the inflation rate of the period.
From the second recurring period here is what you do.
let say the rental per month is $100 and annual increase is 6% then your recurring setup will be as follows:
Year 2 - Feb 23 - Mar 24 - $106 per month
Year 3 - Feb 24 - Mar 25 - $112.36 per month
All these periods you can set them upfront as follows:
@Panashe_Mlambo, you have missed the point. @Davide clearly said that he does not know the inflation factor in advance. So he cannot set up recurring invoices in advance. He is looking for a way to automate the process of updating many recurring invoices once the inflation factor is known.
This tedious problem with recurring-invoices applies across the board for things like subscriptions, rent, and other such non-inventory items, when their prices invariably change.
I think It could be simply solved if recurring-invoices had a “batch update” function included, which probably involves expanding the api.
The subscription date varies with each contact, so must be separately calculated for each contact and
the percentage increase varies with what happens in future years, so can only be calculated in the future when that data is available.
Then I think automation options are limited. If inflation data is annual inflation then this process could be done annually, when the data is released. If it is the inflation over the prior term for each contact, then it would need to be calculated more frequently.
Maintain a list of subscriptions with their current period expiry date. Then before each expiry date, update the recurring invoice (if done within a payment period) or create a new recurring invoice if done more in advance.
Wait for batch update of recurring invoices to be available in Manager, then use that to implement option 1. data entry.
Use the API to implement option 1. data entry.
I agree … It would be very useful to have the possibility to batch create recurring selling invoices.
Also if @lubos can add an option to clone expired recurring selling invoices it would solve part of the issues.