使用EXCEL計算貸款金額

使用PMT公式計算貸款的每月繳款金額，=PMT(月利率 rate , 總期數 nper, 貸款金額 pv)，
月利率rate、總期數nper、貸款金額pv都是正數，正數表示領到金額，PMT算出的數字會是負數，
而負數表示要付出金錢。

如果希望知道繳了一年12期之後，還剩下多少貸款金額尚未償還，
可以利用PMT加上FV兩個公式來合併計算，首先，計算出每期的繳款金額，
然後利用FV，=FV(月利率 rate , 繳費幾期之後 nper, 每期繳款 pmt, 貸款金額 pv)，
FV會算出負數，而負數表示還剩下多少貸款金額尚未償還。

想知道每期繳款中，有多少是繳付本金，有多少是繳付利息，
可以分別使用PPMT與IPMT這兩個公式，=PPMT(月利率 rate,
哪一期 per , 總期數 nper, 貸款金額 pv)，PPMT會算出負數，
而負數表示那期繳款中，償還本金的金額。同格式，IPMT表示那期繳款中，
償還利息的金額。

VB中各種進制的轉換

'-------------------------------------------

' 用途：將十進制轉化為二進制
' 輸入：Dec(十進制數)
' 輸入數據類型：Long
' 輸出：DEC_to_BIN(二進制數)
' 輸出數據類型：String
' 輸入的最大數為2147483647,輸出最大數為1111111111111111111111111111111(31個1)
'-------------------------------------------
Public Function DEC_to_BIN(Dec As Long) As String
DEC_to_BIN = ""
Do While Dec > 0
DEC_to_BIN = Dec Mod 2 & DEC_to_BIN
Dec = Dec \ 2
Loop
End Function
'-------------------------------------------
' 用途：將二進制轉化為十進制
' 輸入：Bin(二進制數)
' 輸入數據類型：String
' 輸出：BIN_to_DEC(十進制數)
' 輸出數據類型：Long
' 輸入的最大數為1111111111111111111111111111111(31個1),輸出最大數為2147483647
'-------------------------------------------
Public Function BIN_to_DEC(ByVal Bin As String) As Long
Dim i As Long
For i = 1 To Len(Bin)
BIN_to_DEC = BIN_to_DEC * 2 + Val(Mid(Bin, i, 1))
Next i
End Function
'-------------------------------------------
' 用途：將十六進制轉化為二進制
' 輸入：Hex(十六進制數)
' 輸入數據類型：String
' 輸出：HEX_to_BIN(二進制數)
' 輸出數據類型：String
' 輸入的最大數為2147483647個字符
'-------------------------------------------
Public Function HEX_to_BIN(ByVal Hex As String) As String
Dim i As Long
Dim B As String

Hex = UCase(Hex)
For i = 1 To Len(Hex)
Select Case Mid(Hex, i, 1)
Case "0": B = B & "0000"
Case "1": B = B & "0001"
Case "2": B = B & "0010"
Case "3": B = B & "0011"
Case "4": B = B & "0100"
Case "5": B = B & "0101"
Case "6": B = B & "0110"
Case "7": B = B & "0111"
Case "8": B = B & "1000"
Case "9": B = B & "1001"
Case "A": B = B & "1010"
Case "B": B = B & "1011"
Case "C": B = B & "1100"
Case "D": B = B & "1101"
Case "E": B = B & "1110"
Case "F": B = B & "1111"
End Select
Next i
While Left(B, 1) = "0"
B = Right(B, Len(B) - 1)
Wend
HEX_to_BIN = B
End Function
'-------------------------------------------
' 用途：將二進制轉化為十六進制
' 輸入：Bin(二進制數)
' 輸入數據類型：String
' 輸出：BIN_to_HEX(十六進制數)
' 輸出數據類型：String
' 輸入的最大數為2147483647個字符
'-------------------------------------------
Public Function BIN_to_HEX(ByVal Bin As String) As String
Dim i As Long
Dim H As String
If Len(Bin) Mod 4 <> 0 Then
Bin = String(4 - Len(Bin) Mod 4, "0") & Bin
End If

For i = 1 To Len(Bin) Step 4
Select Case Mid(Bin, i, 4)
Case "0000": H = H & "0"
Case "0001": H = H & "1"
Case "0010": H = H & "2"
Case "0011": H = H & "3"
Case "0100": H = H & "4"
Case "0101": H = H & "5"
Case "0110": H = H & "6"
Case "0111": H = H & "7"
Case "1000": H = H & "8"
Case "1001": H = H & "9"
Case "1010": H = H & "A"
Case "1011": H = H & "B"
Case "1100": H = H & "C"
Case "1101": H = H & "D"
Case "1110": H = H & "E"
Case "1111": H = H & "F"
End Select
Next i
While Left(H, 1) = "0"
H = Right(H, Len(H) - 1)
Wend
BIN_to_HEX = H
End Function
'-------------------------------------------
' 用途：將十六進制轉化為十進制
' 輸入：Hex(十六進制數)
' 輸入數據類型：String
' 輸出：HEX_to_DEC(十進制數)
' 輸出數據類型：Long
' 輸入的最大數為7FFFFFFF,輸出的最大數為2147483647
'-------------------------------------------
Public Function HEX_to_DEC(ByVal Hex As String) As Long
Dim i As Long
Dim B As Long

Hex = UCase(Hex)
For i = 1 To Len(Hex)
Select Case Mid(Hex, Len(Hex) - i + 1, 1)
Case "0": B = B + 16 ^ (i - 1) * 0
Case "1": B = B + 16 ^ (i - 1) * 1
Case "2": B = B + 16 ^ (i - 1) * 2
Case "3": B = B + 16 ^ (i - 1) * 3
Case "4": B = B + 16 ^ (i - 1) * 4
Case "5": B = B + 16 ^ (i - 1) * 5
Case "6": B = B + 16 ^ (i - 1) * 6
Case "7": B = B + 16 ^ (i - 1) * 7
Case "8": B = B + 16 ^ (i - 1) * 8
Case "9": B = B + 16 ^ (i - 1) * 9
Case "A": B = B + 16 ^ (i - 1) * 10
Case "B": B = B + 16 ^ (i - 1) * 11
Case "C": B = B + 16 ^ (i - 1) * 12
Case "D": B = B + 16 ^ (i - 1) * 13
Case "E": B = B + 16 ^ (i - 1) * 14
Case "F": B = B + 16 ^ (i - 1) * 15
End Select
Next i
HEX_to_DEC = B
End Function
'-------------------------------------------
' 用途：將十進制轉化為十六進制
' 輸入：Dec(十進制數)
' 輸入數據類型：Long
' 輸出：DEC_to_HEX(十六進制數)
' 輸出數據類型：String
' 輸入的最大數為2147483647,輸出最大數為7FFFFFFF
'-------------------------------------------
Public Function DEC_to_HEX(Dec As Long) As String
Dim a As String
DEC_to_HEX = ""
Do While Dec > 0
a = CStr(Dec Mod 16)
Select Case a
Case "10": a = "A"
Case "11": a = "B"
Case "12": a = "C"
Case "13": a = "D"
Case "14": a = "E"
Case "15": a = "F"
End Select
DEC_to_HEX = a & DEC_to_HEX
Dec = Dec \ 16
Loop
End Function
'-------------------------------------------
' 用途：將十進制轉化為八進制
' 輸入：Dec(十進制數)
' 輸入數據類型：Long
' 輸出：DEC_to_OCT(八進制數)
' 輸出數據類型：String
' 輸入的最大數為2147483647,輸出最大數為17777777777
'-------------------------------------------
Public Function DEC_to_OCT(Dec As Long) As String
DEC_to_OCT = ""
Do While Dec > 0
DEC_to_OCT = Dec Mod 8 & DEC_to_OCT
Dec = Dec \ 8
Loop
End Function
'-------------------------------------------
' 用途：將八進制轉化為十進制
' 輸入：Oct(八進制數)
' 輸入數據類型：String
' 輸出：OCT_to_DEC(十進制數)
' 輸出數據類型：Long
' 輸入的最大數為17777777777,輸出的最大數為2147483647
'-------------------------------------------
Public Function OCT_to_DEC(ByVal Oct As String) As Long
Dim i As Long
Dim B As Long

For i = 1 To Len(Oct)
Select Case Mid(Oct, Len(Oct) - i + 1, 1)
Case "0": B = B + 8 ^ (i - 1) * 0
Case "1": B = B + 8 ^ (i - 1) * 1
Case "2": B = B + 8 ^ (i - 1) * 2
Case "3": B = B + 8 ^ (i - 1) * 3
Case "4": B = B + 8 ^ (i - 1) * 4
Case "5": B = B + 8 ^ (i - 1) * 5
Case "6": B = B + 8 ^ (i - 1) * 6
Case "7": B = B + 8 ^ (i - 1) * 7
End Select
Next i
OCT_to_DEC = B
End Function
'-------------------------------------------
' 用途：將二進制轉化為八進制
' 輸入：Bin(二進制數)
' 輸入數據類型：String
' 輸出：BIN_to_OCT(八進制數)
' 輸出數據類型：String
' 輸入的最大數為2147483647個字符
'-------------------------------------------
Public Function BIN_to_OCT(ByVal Bin As String) As String
Dim i As Long
Dim H As String
If Len(Bin) Mod 3 <> 0 Then
Bin = String(3 - Len(Bin) Mod 3, "0") & Bin
End If

For i = 1 To Len(Bin) Step 3
Select Case Mid(Bin, i, 3)
Case "000": H = H & "0"
Case "001": H = H & "1"
Case "010": H = H & "2"
Case "011": H = H & "3"
Case "100": H = H & "4"
Case "101": H = H & "5"
Case "110": H = H & "6"
Case "111": H = H & "7"
End Select
Next i
While Left(H, 1) = "0"
H = Right(H, Len(H) - 1)
Wend
BIN_to_OCT = H
End Function
'-------------------------------------------
' 用途：將八進制轉化為二進制
' 輸入：Oct(八進制數)
' 輸入數據類型：String
' 輸出：OCT_to_BIN(二進制數)
' 輸出數據類型：String
' 輸入的最大數為2147483647個字符
'-------------------------------------------
Public Function OCT_to_BIN(ByVal Oct As String) As String
Dim i As Long
Dim B As String

For i = 1 To Len(Oct)
Select Case Mid(Oct, i, 1)
Case "0": B = B & "000"
Case "1": B = B & "001"
Case "2": B = B & "010"
Case "3": B = B & "011"
Case "4": B = B & "100"
Case "5": B = B & "101"
Case "6": B = B & "110"
Case "7": B = B & "111"
End Select
Next i
While Left(B, 1) = "0"
B = Right(B, Len(B) - 1)
Wend
OCT_to_BIN = B
End Function
'-------------------------------------------
' 用途：將八進制轉化為十六進制
' 輸入：Oct(八進制數)
' 輸入數據類型：String
' 輸出：OCT_to_HEX(十六進制數)
' 輸出數據類型：String
' 輸入的最大數為2147483647個字符
'-------------------------------------------
Public Function OCT_to_HEX(ByVal Oct As String) As String
Dim Bin As String
Bin = OCT_to_BIN(Oct)
OCT_to_HEX = BIN_to_HEX(Bin)
End Function
'-------------------------------------------
' 用途：將十六進制轉化為八進制
' 輸入：Hex(十六進制數)
' 輸入數據類型：String
' 輸出：HEX_to_OCT(八進制數)
' 輸出數據類型：String
' 輸入的最大數為2147483647個字符
'-------------------------------------------
Public Function HEX_to_OCT(ByVal Hex As String) As String
Dim Bin As String
Hex = UCase(Hex)
Bin = HEX_to_BIN(Hex)
HEX_to_OCT = BIN_to_OCT(Bin)
End Function


VB自帶函數：
十進制轉八進制：Oct(num)
十六進制轉八進制：oct("&H" &amp; num)
十進制轉十六進制：hex(num)
八進制轉十六進制：hex("&O" & num)

十六進制轉換為十進制
Dim str As String
str = Text2.Text
Text10.Text = CLng("&H" & str)

新版Blogger試用!!

HI~體驗一下 "部落格" 試試新版Blog功能

雖然有一陣子沒用了,還是試用一下^^

[防毒防駭]AOL(美國線上推出Active Virus Shield卡巴斯基核心的免費掃毒軟體

AOL(美國線上推出)Active Virus Shield(卡巴斯基核心的免費掃毒軟體)
AOL(美國線上)於八月份在自家網站上推出Active Virus Shield這套有著
卡巴斯基核心的免費掃毒軟體，跟以往卡巴斯基令使用者詬病的極佔資源
及拖慢開機速度這兩個大問題，在這套軟體並不復見，而且佔用資源極小
使用者只需用自己的Email信箱去AOL申請，就可以獲得一年的KEY
八月份新出爐的掃毒排行榜裡獲得第二名.....第一名當然還是卡巴
不過就以免費,免破解和吃資源的角度來看算是相當不錯?
有興趣的可以試用看看?
下載點(輸入信箱會自動寄給你一組啟動碼!):
http://www.activevirusshield.com/antivirus/freeav/index.adp?
中文化(要進入安全模式才可中文化!!)
http://feelingwishes.myweb.hinet.net/Active Virus Shield(cht).exe

BLOG網址

http://isanhsu.blogspot.com

2006/02/15 後~

情人節的後一天,天氣變的更好了,雖然沒有在情人節當天過節,但是...