Blog

Some useful VBAs

Below are some useful VBAs I have recently used in by spreadsheets. I am publishing them as it may help others struggling with VBA code as I often do 🙂

Sub HideRows()
    ‘This command hides all the rows from 2 to 100.
    ‘I have not hidden row 1 as it has my headings.
Rows(“2:100”).EntireRow.Hidden = True
‘This command unhides the active row
Rows(ActiveCell.Row).EntireRow.Hidden = False
End Sub

Sub UnhideRows()
    ‘This command Unhides all the rows
Rows(“2:100”).EntireRow.Hidden = False
End Sub

How to assign short keys?

By assigning keyboard shortcut keys you can make is easier to run these macros.
Developer > Macros >Options> Shortcut Key

Below is a sample macro I have created for a daily task.

Sub BankFlash()

   ‘Define the variable for sheet name
Dim sname As Single

    ‘Assigning the variable with the value of Active Sheet’s number
sname = ActiveSheet.Name

    ‘ Copying Active Sheet and then telling it to paste just after the active sheet
ActiveSheet.Copy After:=ActiveSheet

    ‘ Now the Active sheet is the new sheet and we are increasing the sheet name by 4 if its  a Monday.   

If Weekday(Now) = 2 Then
ActiveSheet.Name = sname + 4
Else

‘ On days other than Monday, we increase the sheet name by 1   
ActiveSheet.Name = sname + 1
End If

‘Active sheet is the new sheet now.
‘Clears contents from specific cells in the new sheet
Range(“B7:d12”).ClearContents
Range(“B17:d24”).ClearContents

‘Clears contents from specific cells in the new sheet if today is Monday
If Weekday(Now) = 2 Then
Range(“H19:H23”).ClearContents
Else
End If

‘Links the opening balance to the previous sheet             

Range(“B4”) = “=” & Str(sname) & “!B29”
Range(“C4”) = “=” & Str(sname) & “!C29”
Range(“D4”) = “=” & Str(sname) & “!D29”

‘Last Step , this leaves the control on the box from where i wish to start inputting data
Range(“B31”).Select

End Sub

Custom functions

The below macro creates a FUNCTION for excel to add numbers by colours. I still donot know how it works fully but I find it useful sometimes.

Function SumByColor(CellColor As Range, SumRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In SumRange
If ICol = TCell.Interior.ColorIndex Then
SumByColor = SumByColor + TCell.Value
End If
Next TCell
End Function

Bonus

1. how to Unprotect an Excel Spreadsheet if you have lost your password.

2. Sometimes while using filters dates donot group together. Simply multiply all dates by 1 , using paste special function.

National Minimum Wage is increasing from 1st April 2022

Every year all employers should check payroll records to ensure they donot fall foul of NMW regualtions

National Minimum Wage (NMW) is increasing from ÂŁ8.91 to ÂŁ9.50 from 1st April 2022.

More information on NMW as individuals younger than 23 years get lower pay.

Please review your payroll records to ensure that you are not paying any staff less than NMW.

Method to check

  1. Fixed Pay – Annual Pay divided by number of hours worked in a year.

Example an employee works for ÂŁ24,000 per annum for 35 hours per week.

Annual hours worked 35 x 52 = 1820 hours.

Hourly rate = 24,000 divided by 1820 = ÂŁ13.19. This is more than NMW

  1. Variable pay – this is simpler just ensure basic hourly rate is £9.50 or more from 1st April 2022.

Gov.uk calculator to check if you are paying NMW

Taxation of investments in Indian Mutual Funds in the UK

Gains made on disposal of Indian mutual funds are taxed at highest marginal rate of taxation

HMRC has classified overseas mutual funds as `reporting` and `non-reporting`.

Reporting means mutual funds which provided certain data to HMRC on periodic basis. HMRC publishes a list of these funds monthly. In case your mutual fund is such a fund your gain will be taxed as Capital Gains.

Non-reporting means any mutual funds which do not comply with these requirements.

Recently funds in India have started registering with HMRC and have become reporting funds so we recommend you check HMRC list before tax calculation. For a list of reporting offshore funds click here.

When you dispose offshore non-reporting mutual funds

Any gain on disposal of investments in Offshore non-reporting mutual funds (i.e., any fund based outside UK) will be taxed at the highest marginal rate of income tax and not as capital gains.

Double whammy – in case of loss, the loss is only allowed to be set-off against capital gains and not against income .

Lastly, annual capital exemption is also not available to such gains.

Conclusion: From a tax perspective, if you wish to invest in Indian stock market better invest directly in stock and shares and not via a Mutual fund.

Bonus:

  1. An article with an example.
  2. This is a complex area of law; further information can be seen at HMRC Investment Funds Manual – IFM12000 and IFM13000.
  3. Offshore gain is treated for tax purposes as miscellaneous income – [see Tolley Income tax annual 50.3] to be mentioned in SA106 2023 in Box 41.

Further reading:
1. HS265 Offshore Funds
2. Visit our Worldwide Disclosure blog.
3. To know about taxation of UK mutual funds.

Incentive for manufacturers in UK

UK government does not support its manufacturing and farming industries like other countries.

Current incentives are listed below:

  1. Super deduction

Only Companies that incur qualifying expenditures between 1 April 2021 to 31 March 2023, can claim:

  • a super-deduction allowance of 130% on most new plant and machinery investments that ordinarily qualify for 18% main rate writing down allowances
  • a first-year allowance of 50% on most new plant and machinery investments that ordinarily qualify for 6% special rate writing down allowances

Thus, super-deduction of 130% will provide a deduction exceeding the cost of the asset and first year deduction of 50% will accelerate allowances.

Exclusions

  1. Used and second hand will not qualify.
  2. It should not be a car
  3. Expenditures on contracts entered into prior to 3 March 2021 even if expenditures are incurred after 1 April 2021. 
  4. Plant and machinery expenditure which is incurred under a Hire Purchase or similar contract must meet additional conditions to qualify

Source:

ACCA guidance

2. Research & Development

This is more difficult to qualify but has higher rewards. If a company incurs expenditure to make an advance in science or technology, it can claim R&D relief:

  • deduct an extra 130% of their qualifying costs from their yearly profit, as well as the normal 100% deduction, to make a total 230% deduction
  • claim a tax credit if the company is loss making, worth up to 14.5% of the surrenderable loss

Source

Gov.uk

3. Government support directory

UK Government has made a helpful directory where businesses can find funding in shape of grants, equity and debt. Access it via link

4. Local chamber of commerce

One can join local chamber of commerce to meet similar businesses and share ideas and contacts.

Remittance out of India

There are two main types of accounts in India. NRE and NRO both are maintained in INR.

 NRENRO
PurposeFor sending monies to India from another countryFor funds generated in India like rental income
RepatriationFully repatriable i.e. all funds in this account can be sent out of India without any restrictions.Repatriation limit of USD one million per financial year.   Plus, paperwork needs to be completed before sending funds out of India.1

Paperwork to remit funds out of India or to convert NRO funds into NRE:

  1. NRE/NRO remittance forms
    NRE does not need any other documents, documents listed below are for NRO:
  2. Form A2  – Funds transfer form. Purpose code to use it S1301 – Remittance for family maintenance and savings
  3. Please ask the Bank for the BSR Code for the Branch. Indian CA will need it.
  4. Form 15CB – Chartered Accountant certificate from Indian CA
  5. Form 15 CA – Self declaration from Indian CA
  6. UDIN from Indian CA
  7. Source of funds like contract notes of share sale etc. from client or his Indian broker.

    Bankers will need original wet signature forms and self-attested documents so allow sufficient time for courier.

A useful blog I found on this subject.

August 2021

Recently a client wished to invest funds in the Indian stock market and he contacted his old bank where he had a dormant account, after lot of paperwork and telephone calls finally the bank was able to activated NRO accounts and client started sending funds to India in his NRO account.

We realised that this is not the optimum solution for the client as he is sending his overseas earnings to India and will face restrictions in the future if funds are sent via NRO account, see above for restrictions.

We have requested the banker to change the arrangement to NRE account, let see how things turn up.

November 2021

NRE PIS account opened after months of to and fro.

December 2021

Client wanted to transfer shares held in NRO account to NRE account but due to lack of clear rules, this project was abandoned.