How many of these 7 currency problems in rent rolls have you come across?
- Inconsistent currency formatting
- Mixing currencies in a single column
- Missing or incorrect currency conversion rates
- Dynamically updating currency rates
- Rounding errors
- Date-specific currency value
- Handling currency in Excel formulas and functions
One? Four? Quite possibly all of them if you work with cross-border portfolios.
For commercial real estate analysts, rent roll spreadsheets are as vital as oxygen for humans. You just can’t survive without them. Keeping track of rent rolls is crucial for evaluating the performance of a property, its investment potential and its overall profitability.
However, things can get tricky when you’re dealing with different currencies. It’s like dealing with a multilingual translator for your spreadsheets, but one who doesn’t always get it right.
We’re here to help with a simple guide to fix these critical issues and get your spreadsheets working smoothly.
Avoid these 7 most common currency problems in rent rolls
After managing thousands of tenancy schedules for clients, here are the currency problems in rent rolls that pop up time and again.
1. Inconsistent currency formatting
If the spreadsheet contains data from different sources or regions, there might be inconsistencies in how currencies are formatted.
For instance, some entries might use symbols ($, €, £), while others use ISO codes (USD, EUR, GBP), making it difficult to perform accurate calculations as spreadsheets often rely on formulas and functions to perform calculations automatically.
These functions typically expect consistent currency formatting to work correctly. Inconsistencies in currency symbols or codes can cause errors in these calculations or formulas, as they may not recognise the different formats or interpret them correctly.
2. Mixing currencies in a single column
One of the main problems with rent rolls is when you have a single column with amounts in multiple currencies.
This issue often occurs when managing a portfolio of properties across different countries or regions, where different currencies are in use. It can lead to confusion, inaccurate calculations, and reporting errors.
For example, you might see three rows with 3400 CZK, 280 PLN, and 620 DKK. When someone copies and pastes the column of numbers, usually the currency abbreviations are left off and you end up with a column of numbers without knowing the currency they’re in.
3. Missing or incorrect currency conversion rates
When dealing with international properties or transactions, currency conversion rates are essential for accurate financial analysis. However, if the spreadsheet doesn’t have conversion rates, it can lead to inaccurate calculations and projections.
As an example, an analyst might mistakenly use the USD/EUR exchange rate instead of the USD/GBP rate when converting rents for a property in London, leading to an incorrect calculation.
4. Dynamically updating currency rates
Currency exchange rates can fluctuate regularly and, if the spreadsheet automatically updates these rates, it can lead to continuously moving figures.
In some financial settings, real-time or regularly updated exchange rate data is crucial to reflect the most accurate financial figures but, in real estate, it’s usually preferred to set a single rate for a period (month or quarter) company-wide.
5. Rounding errors
These might seem insignificant at the individual level, but when dealing with large rent rolls, these can add up and cause significant discrepancies.
If a property in Canada has a rent of CAD 1499.75, it might be tempting to round this to CAD 1500 for simplicity. However, when this rounding is applied to a large number of units or over a long period, it can result in a significant discrepancy in the total rent calculation.
6. Date-specific currency value
Depending on the contract, some rents might need to be calculated based on the exchange rate at a specific date. If the exchange rate data for that date isn’t available, it could lead to inaccuracies.
For example, if a lease agreement was signed on January 1, 2022, with rent listed in euros, the analyst might need to use the exchange rate from that specific date to convert the rent to USD. If the analyst only has access to today’s exchange rate, this could lead to inaccuracies.
Read Excel rent rolls: date format problems for more about errors with dates.
7. Handling currency in Excel formulas and functions
Excel does not inherently consider currency in formulas and functions, which can lead to incorrect calculations if not properly managed.
For instance, an average function might simply average the numbers without considering the different currencies involved. If an analyst wants to average the rent prices of properties in the US (in USD) and the UK (in GBP) and they simply use Excel’s AVERAGE function without first converting all rents to the same currency, the result will be incorrect and misleading.
For example, an average of $1500 and £1200 (which is higher than $1500 as per exchange rate) might yield a result lower than $1500, which is inaccurate.
How to fix these 7 currency problems in rent rolls
To mitigate these currency problems, real estate analysts can take the following steps.
1. Standardise currency formatting
Format all currency values in the spreadsheet consistently. We’d recommend using ISO codes in a new column uniformly throughout the dataset. For example, use USD for US dollars, EUR for euros and GBP for British pounds sterling consistently across all entries.
2. Ensure accurate currency identification
Clearly identify and label the amount column and the corresponding currency column in the rent roll table. This is especially useful when you receive rent in one currency (e.g. EUR) and operating expenses in the home currency (e.g. PLN).
Or you could establish a standardised process for converting all currencies into a common base currency. This allows for consistent reporting and simplifies the aggregation of rental income.
3. Include conversion-rate data in the spreadsheet
If the spreadsheet doesn’t have conversion rates, you can manually include a separate section or tab that contains the relevant conversion rates. Update it regularly to ensure accuracy. When performing currency conversions, reference this section to get the correct conversion rates.
4. Use centralised exchange rates
Convert provided figures back into a master currency as per the rates used in the source file. Then you can convert currencies in this and all other files using consistent rates.
5. Don’t round numbers in calculations
Use rounding for presentation purposes, though, but let stakeholders know that rounding is applied only for display purposes and that the actual calculations use precise values. This will help manage expectations and avoid confusion or misunderstandings.
6. Collect historical exchange-rate data
Clearly document the reference date for each rent calculation that requires a specific exchange rate and/or obtain access to historical exchange rate data from reliable sources or financial institutions.
For other date-related solutions in spreadsheets, read Excel rent rolls: date format problems.
7. Accurately handle currencies in spreadsheets
You’ve got several options here: standardise the currency formatting, use currency conversion functions, separate calculations by currency, apply appropriate cell formatting, validate with sample data, and consider conditional logic (using IF statements or other logical functions to conditionally apply the appropriate calculations based on the currency being considered). You could also normalise values, update rates, and separate presentation from calculations.
Automation for currency problems in rent rolls
The above 7 measures will improve your financial analysis, tackle currency problems, and help you manage rent rolls easily.
And if you use automation software, like PRODA, you can not only save time but you can:
- correctly identify and calculate currencies, and standardise your multi-currency rent roll data;
- error-check your rent roll data and catch inconsistencies, inaccuracies or missing information;
- set exchange rates, view historical rates and see tracked changes;
- get tailored reports with different currency settings to cater to stakeholder needs.
Removing currency problems in rent rolls
The unique circumstances of each rent roll will determine how you confront these currency-related demons. Remember, though, that manually processing rent rolls needs time, concentration and good processes to ensure consistency in your property reporting across your business operations.
If, however, you’re looking for greater consistency and accuracy, automated processing of rent rolls could bring the clarity you need. Talk to PRODA about how we can help you translate your multi-currency rent rolls into a clear language that everybody understands.
