Post by james on Feb 2, 2016 3:44:18 GMT
This outlines how you can do the calculations needed for a UK tax return if you want or need to do fully perfect daily exchange rate calculations. There are shortcuts if monthly or annual will be close enough for you to complete a correct enough tax return.
Two short cuts that you may want to use:
1. You must provide per-country breakdowns of interest if you have total foreign interest income of £2,000 or more. If less, you don't need to do that extra work.
2. You can use annual average or monthly average exchange rates if that is close enough. Within £10 is definitely close enough and that means up to about £300 of total interest to use an annual average, £200 to be safer.
Unfortunately without notice Bondora withdrew the online reporting that allowed completion of UK tax returns via the XLS downloads from the now removed cash flow page, though this did require you to have loans to only one country unless interest was under 2k. Copy and paste version of this was restored on 4 February 2016.
The closest you can get without spreadsheet work is to go to expert center and use the historical cash flow. Add the numbers in the net interest income and fees paid columns to get the gross interest received for each month. That will get you the whole month numbers.There is currently no way online to get the interest paid each day to complete a UK tax return with daily interest rates. Also no way to handle the 1-5 and 6-on parts of April tax year split. From the 4 February update daily figures are available in the historical (and future) cash flow so you can do the required 1-5/6+ April tax year split. XLS download still unavailable but it's a welcome return for me!
You can get interest paid to date numbers from the investments tab. In the investment filter section use the "Status" selection and in turn pick each of Current, Overdue, 60+ days overdue, Repaid and Released. Also for all of those (- selection) for loans with Secondary market status Sold investments. For each look at the column Repaid interests / late charges and look at the bottom row which gives the total for every page of the output so you only need to look at the first page. You can compare this to previous totals to get the amount for some other period. You can use this to do the April split if you carefully time when you get the information.
To do a UK tax return now that Bondora hasremoved limited one fairly easy way you may need to do more work in the Data export part of the Expert center page. I suggest that you get all of the reports:
I'll probably use the numbers from the account statement rather than the repayments, but might use both to cross-check. The major catch with both of these is that they do not tell you the country so if you have lent to more than one country you need to do more work still, to look up the country for every payment from your list of borrowers.
So to start you could create a new sheet that just takes the numbers from the account statement and adds the country looked up from the borrower information. That can then be assigned using a formula to one cell per country per row and type of payment if it's from that country. Once you have the country columns for each type of payment you can then create another sheet that uses sumif for each day of the year to give you the total amount of each type of payment for each day for each country. You can add cells there to also do currency conversion based on ECB daily exchange rates. Finally, you can do an annual total sheet for each country that will give you the final numbers which you need for the tax return.
Don't do any of the calculation or summing in the original sheets that contain the data from Bondora. If you avoid that you will be able to replace them with new versions as you get them from Bondora and for new years, saving a lot of time later.
I haven't done that work yet because I used the cash flow numbers and knew that I only have one country to deal with, so I can skip the currency lookup parts.
Some exchange rate sources: annual average, monthly average (HMRC for duty, or Banque de France) and daily ECB rates, reporting the daily rate version to HMRC. Links take you to a source of each of those.
Two short cuts that you may want to use:
1. You must provide per-country breakdowns of interest if you have total foreign interest income of £2,000 or more. If less, you don't need to do that extra work.
2. You can use annual average or monthly average exchange rates if that is close enough. Within £10 is definitely close enough and that means up to about £300 of total interest to use an annual average, £200 to be safer.
Unfortunately without notice Bondora withdrew the online reporting that allowed completion of UK tax returns via the XLS downloads from the now removed cash flow page, though this did require you to have loans to only one country unless interest was under 2k. Copy and paste version of this was restored on 4 February 2016.
The closest you can get without spreadsheet work is to go to expert center and use the historical cash flow. Add the numbers in the net interest income and fees paid columns to get the gross interest received for each month. That will get you the whole month numbers.
You can get interest paid to date numbers from the investments tab. In the investment filter section use the "Status" selection and in turn pick each of Current, Overdue, 60+ days overdue, Repaid and Released. Also for all of those (- selection) for loans with Secondary market status Sold investments. For each look at the column Repaid interests / late charges and look at the bottom row which gives the total for every page of the output so you only need to look at the first page. You can compare this to previous totals to get the amount for some other period. You can use this to do the April split if you carefully time when you get the information.
To do a UK tax return now that Bondora has
- investments list (figures so far for each investment you made)
- monthly overview (useful summary by month containing what was in the money page)
- income report (limited value for a UK return, it consolidates all days into one number, so you can't do daily or monthly exchange rate calculations, but it would be useful for those in the EU or not doing daily conversion. You could get it for each month if using monthly exchange rate conversions, or for the year if annual exchange rate is good enough for your return)
- repayments (one you might use, every payment made with day, principal, interest and fee/penalty columns. Use a spreadsheet, database or other tool to consolidate to daily figures then do the currency conversion each day. But no country info),
- planned future cash flows,
- secondary market
- account statement (all the individual transactions, including fees, another way to get the numbers for the tax return, better than repayments so you get the fees, but still no country info)
I'll probably use the numbers from the account statement rather than the repayments, but might use both to cross-check. The major catch with both of these is that they do not tell you the country so if you have lent to more than one country you need to do more work still, to look up the country for every payment from your list of borrowers.
So to start you could create a new sheet that just takes the numbers from the account statement and adds the country looked up from the borrower information. That can then be assigned using a formula to one cell per country per row and type of payment if it's from that country. Once you have the country columns for each type of payment you can then create another sheet that uses sumif for each day of the year to give you the total amount of each type of payment for each day for each country. You can add cells there to also do currency conversion based on ECB daily exchange rates. Finally, you can do an annual total sheet for each country that will give you the final numbers which you need for the tax return.
Don't do any of the calculation or summing in the original sheets that contain the data from Bondora. If you avoid that you will be able to replace them with new versions as you get them from Bondora and for new years, saving a lot of time later.
I haven't done that work yet because I used the cash flow numbers and knew that I only have one country to deal with, so I can skip the currency lookup parts.
Some exchange rate sources: annual average, monthly average (HMRC for duty, or Banque de France) and daily ECB rates, reporting the daily rate version to HMRC. Links take you to a source of each of those.
