I am trying to figure out the best method on creating new loan number that are 10 characters in length starting with:
000030000
000030001 etc..
by adding one valid digit using the Luhn method; so here is my scenario:

When I do the calculations myself manually for the examples above; number are valid
Loan Number Validity
0000300004 Valid
0000300012 Valid

The validity code I am using is:
=(--RIGHT( B1,1)=(10-MOD(SUMPRODUCT(--MID("01234567891x3x5x7x9",MID(B1,ROW(INDIRECT("1:"&(LEN( B1 )-1))),1)*(1+MOD(ROW(INDIRECT("1:"&(LEN( B1)-1))),2))+1,1)),10)))

As for last the digit; if I use a the following formula it provides me a wrong number making my loan number invalid:
=MOD(1000-SUM(IF(MOD(ROW(INDIRECT("1:"&LEN(A2))),2),3,1)*MID(A2,LEN(A2)-ROW(INDIRECT("1:"&LEN(A2)))+1,1)),10)

Loan Number Validity
0000300007 Invalid
0000300016 Invalid

Can someone help me with the correct Luhn algorithm excel formula to make my loan numbers valid so that I don't have to do these manually?
I appreciate any input; thank in advance!